[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_TOOLS
Source
1 PACKAGE BODY JTF_IH_TOOLS AS
2 /* $Header: JTFIHPTB.pls 120.2 2006/01/10 00:12:14 nchouras noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_TOOLS';
4
5 -- Created by IAleshin, based on Enh#3519691
6 --
7 PROCEDURE MIGRATE_IH_WRAPUPS(p_Wrap_Up_Level VARCHAR2 ) AS
8 l_Count NUMBER;
9 l_Combinations NUMBER := 0;
10 l_Wrap_Up_Level VARCHAR2(2000);
11 l_Wrap_Up_Id NUMBER;
12 l_Sql VARCHAR2(2000);
13 TYPE t_RefCur IS REF CURSOR;
14 v_RetCursor t_RefCur;
15 TYPE rec_bind_Params IS RECORD
16 (
17 Name VARCHAR2(30),
18 Value VARCHAR2(2000)
19 );
20 TYPE t_Params IS TABLE OF rec_bind_Params INDEX BY BINARY_INTEGER;
21 arr_Params t_Params;
22 l_Params BINARY_INTEGER := 0;
23 l_Cursor BINARY_INTEGER;
24 l_Res BINARY_INTEGER;
25
26 l_outcome_id NUMBER;
27 l_result_id NUMBER;
28 l_reason_id NUMBER;
29 l_source_code VARCHAR2(2000);
30 l_source_code_id NUMBER;
31 l_active_outcome VARCHAR2(1);
32 l_active_result VARCHAR2(1);
33 l_active_reason VARCHAR2(1);
34 l_result_required VARCHAR2(1);
35 l_reason_required VARCHAR2(1);
36 l_object_id NUMBER;
37 l_object_type VARCHAR2(2000);
38 b_Add_Wrap BOOLEAN;
39 b_level_change_required BOOLEAN;
40 b_end_Date_required BOOLEAN;
41 l_end_date_time DATE;
42 l_wrap_id NUMBER;
43 l_Check_Level VARCHAR2(30);
44 e_Error EXCEPTION;
45 e_Skip EXCEPTION;
46 l_active_flag VARCHAR2(1);
47 BEGIN
48 l_active_flag := 'Y';
49 l_Wrap_Up_Level := p_Wrap_Up_Level;
50
51 l_Sql := 'SELECT distinct tbl.outcome_id, outc.active active_outcome, '||
52 'outc.result_required result_required, '||
53 'tbl.result_id, result.active active_result, '||
54 'result.result_required reason_required, '||
55 'tbl.reason_id, reason.active active_reason, '||
56 'tbl.source_code, '||
57 'tbl.source_code_id FROM ';
58
59 SAVEPOINT MIGRATE_IH_WRAPUPS;
60
61 IF p_Wrap_Up_Level = 'INTERACTION' THEN
62 l_Sql := l_Sql ||'jtf_ih_interactions tbl, ';
63 l_Check_Level := 'ACTIVITY';
64 ELSIF p_Wrap_Up_Level = 'ACTIVITY' THEN
65 l_Sql := l_Sql ||'jtf_ih_activities tbl, ';
66 l_Check_Level := 'INTERACTION';
67 ELSE
68 RAISE e_Error;
69 END IF;
70 --DBMS_OUTPUT.put_line('p_Wrap_Up_Level '|| p_Wrap_Up_Level);
71
72 l_Sql := l_Sql||'jtf_ih_outcomes_b outc, '||
73 'jtf_ih_results_b result, '||
74 'jtf_ih_reasons_b reason '||
75 'WHERE tbl.outcome_id = outc.outcome_id AND '||
76 'tbl.result_id = result.result_id(+) AND '||
77 'tbl.reason_id = reason.reason_id(+) ';
78
79 --DBMS_OUTPUT.put_line(substr(l_Sql,1,255));
80 --DBMS_OUTPUT.put_line(SUBSTR(l_Sql,256,255));
81
82 OPEN v_RetCursor FOR l_Sql;
83 LOOP
84 FETCH v_RetCursor INTO
85 l_outcome_id,
86 l_active_outcome,
87 l_result_required,
88 l_result_id,
89 l_active_result,
90 l_reason_required,
91 l_reason_id,
92 l_active_reason,
93 l_source_code,
94 l_source_code_id;
95
96 EXIT WHEN v_RetCursor%NOTFOUND;
97 IF l_outcome_id = fnd_api.g_miss_num THEN l_result_id := NULL; END IF;
98 IF l_result_id = fnd_api.g_miss_num THEN l_result_id := NULL; END IF;
99 IF l_reason_id = fnd_api.g_miss_num THEN l_reason_id := NULL; END IF;
100 IF l_source_code = fnd_api.g_miss_char THEN l_source_code := NULL; END IF;
101 IF l_source_code_id = fnd_api.g_miss_num THEN l_source_code_id := NULL; END IF;
102 BEGIN
103 BEGIN
104 -- IF l_active_outcome IS NULL OR l_active_result IS NULL OR l_active_reason IS NULL THEN
105 --
106 IF l_active_outcome IS NULL OR (l_result_id IS NOT NULL AND l_active_result IS NULL)
107 OR (l_reason_id IS NOT NULL AND l_active_reason IS NULL)
108 OR (l_result_required = 'Y' and l_result_id is NULL)
109 OR (l_reason_required = 'Y' and l_reason_id is NULL)
110 OR (l_reason_id IS NOT NULL AND l_result_id IS NULL) THEN
111 RAISE e_Skip;
112 END IF;
113
114 -- Check if the source code exists
115 --
116 l_object_type := NULL;
117 l_object_id := NULL;
118
119 IF l_source_code_id IS NOT NULL THEN
120 BEGIN
121 SELECT source_code, ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID
122 INTO l_source_code, l_object_type, l_object_id
123 FROM AMS_SOURCE_CODES
124 WHERE source_code_id = l_source_code_id
125 and active_flag = l_active_flag;
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 RAISE e_Skip;
130 END;
131 ELSE
132 BEGIN
133 IF l_source_code IS NOT NULL THEN
134 SELECT source_code_id, ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID
135 INTO l_source_code_id, l_object_type, l_object_id
136 FROM AMS_SOURCE_CODES
137 WHERE source_code = l_source_code
138 AND active_flag = l_active_flag;
139
140 END IF;
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 RAISE e_Skip;
144 END;
145 END IF;
146 EXCEPTION
147 WHEN TOO_MANY_ROWS THEN
148 --dbms_output.put_line('l_source_code = '||l_source_code||' l_object_type='||l_object_type||' l_object_id='||l_object_id);
149 NULL;
150 END;
151 -- check if record exists and get it's current wrap_up_level
152 BEGIN
153 arr_Params.DELETE;
154 l_Params := 0;
155 l_Sql := 'SELECT wrap_id as wrap_id, wrap_up_level as wrap_up_level '||
156 'FROM jtf_ih_wrap_ups WHERE outcome_id = :outcome_id ' ;
157 l_Params := l_Params + 1;
158 arr_Params(l_Params).NAME := ':outcome_id';
159 arr_Params(l_Params).VALUE := l_outcome_id;
160 IF l_result_id IS NOT NULL THEN
161 l_Sql := l_Sql || ' AND result_id = :result_id';
162 l_Params := l_Params + 1;
163 arr_Params(l_Params).NAME := ':result_id';
164 arr_Params(l_Params).VALUE := l_result_id;
165 ELSE
166 l_Sql := l_Sql || ' AND result_id IS NULL ';
167 END IF;
168
169 IF l_reason_id IS NOT NULL THEN
170 l_Sql := l_Sql || ' AND reason_id = :reason_id ';
171 l_Params := l_Params + 1;
172 arr_Params(l_Params).NAME := ':reason_id';
173 arr_Params(l_Params).VALUE := l_reason_id;
174 ELSE
175 l_Sql := l_Sql || ' AND reason_id IS NULL ';
176 END IF;
177
178 IF l_source_code_id IS NOT NULL THEN
179 l_Sql := l_Sql || ' AND source_code_id = :source_code_id ';
180 l_Params := l_Params + 1;
181 arr_Params(l_Params).NAME := ':source_code_id';
182 arr_Params(l_Params).VALUE := l_source_code_id;
183 ELSE
184 l_Sql := l_Sql || ' AND source_code_id IS NULL AND source_code IS NULL ';
185 END IF;
186 l_Cursor := DBMS_SQL.open_cursor;
187 DBMS_SQL.parse(l_Cursor, l_Sql, DBMS_SQL.native);
188 DBMS_SQL.define_column(l_Cursor, 1, l_wrap_id);
189 DBMS_SQL.define_column(l_Cursor, 2, l_wrap_up_level, 30);
190 FOR i IN 1..arr_Params.COUNT LOOP
191 DBMS_SQL.bind_variable(l_Cursor,arr_Params(i).NAME, arr_Params(i).VALUE);
192 END LOOP;
193 IF DBMS_SQL.execute_and_fetch(l_cursor) <> 0 THEN
194 DBMS_SQL.column_value(l_Cursor, 1, l_wrap_id);
195 DBMS_SQL.column_value(l_Cursor, 2, l_wrap_up_level);
196 ELSE
197 DBMS_SQL.close_cursor(l_cursor);
198 RAISE NO_DATA_FOUND;
199 END IF;
200 DBMS_SQL.close_cursor(l_cursor);
201
202 b_Add_Wrap := FALSE;
203 IF l_wrap_up_level = l_Check_Level THEN
204 b_level_change_required := TRUE;
205 ELSE
206 b_level_change_required := FALSE;
207 END IF;
208
209 IF l_active_outcome = 'N' OR (l_result_id IS NOT NULL AND l_active_result = 'N')
210 OR (l_reason_id IS NOT NULL AND l_active_reason = 'N') THEN
211 b_end_date_required := TRUE;
212 l_end_date_time := SYSDATE;
213 ELSE
214 b_end_date_required := FALSE;
215 l_end_date_time := NULL;
216 END IF;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 b_Add_Wrap := TRUE;
220 WHEN TOO_MANY_ROWS THEN
221 b_Add_Wrap := FALSE;
222 END;
223 IF b_Add_Wrap THEN
224 --dbms_output.put_line('Add!!! '||l_outcome_id||' '||l_result_id||' '||nvl(l_reason_id,-1)||' '||l_source_code_id||' '||l_source_code||' '||l_object_id||' '||l_object_type||' '||p_Wrap_Up_Level);
225 INSERT INTO jtf_ih_wrap_ups (
226 WRAP_ID,
227 OUTCOME_ID,
228 RESULT_ID,
229 REASON_ID,
230 OBJECT_ID,
231 OBJECT_TYPE,
232 SOURCE_CODE_ID,
233 SOURCE_CODE,
234 WRAP_UP_LEVEL,
235 START_DATE,
236 END_DATE,
237 CREATED_BY,
238 CREATION_DATE,
239 LAST_UPDATED_BY,
240 LAST_UPDATE_DATE,
241 LAST_UPDATE_LOGIN
242 )
243 VALUES (
244 JTF_IH_WRAP_UPS_S1.NEXTVAL,
245 l_outcome_id,
246 l_result_id,
247 l_reason_id,
248 l_object_id,
249 l_object_type,
250 l_source_code_id,
251 l_source_code,
252 p_Wrap_Up_Level,
253 SYSDATE,
254 l_end_date_time,
255 fnd_global.user_id,
256 SYSDATE,
257 fnd_global.user_id,
258 SYSDATE,
259 fnd_global.login_id
260 );
261 ELSE
262 IF b_end_Date_required OR b_level_change_required THEN
263 l_Sql := 'UPDATE jtf_ih_wrap_ups SET ';
264 IF b_end_Date_required THEN
265 l_Sql := l_Sql || ' END_DATE = :end_date_time ';
266 IF b_level_change_required THEN
267 --dbms_output.put_line('BOTH! for '||l_wrap_id);
268 l_Sql := l_Sql || ', WRAP_UP_LEVEL = ''BOTH'' ';
269 END IF;
270 ELSE
271 IF b_level_change_required THEN
272 --dbms_output.put_line('BOTH! for '||l_wrap_id);
273 l_Sql := l_Sql || 'WRAP_UP_LEVEL = ''BOTH'' ';
274 END IF;
275 END IF;
276 l_Sql := l_Sql || 'WHERE WRAP_ID = :wrap_id';
277 --dbms_output.put_line(l_Sql);
278
279 IF b_end_Date_required THEN
280 execute IMMEDIATE l_Sql USING l_end_date_time, l_wrap_id;
281 ELSE
282 execute IMMEDIATE l_Sql USING l_wrap_id;
283 END IF;
284 END IF;
285 NULL;
286 END IF;
287 EXCEPTION
288 WHEN e_Skip THEN
289 NULL;
290 END;
291 END LOOP;
292 EXCEPTION
293 WHEN e_Error THEN
294 ROLLBACK TO MIGRATE_IH_WRAPUPS;
295 WHEN OTHERS THEN
296 ROLLBACK TO MIGRATE_IH_WRAPUPS;
297 raise_application_error(-20001,SQLERRM);
298 END MIGRATE_IH_WRAPUPS;
299
300
301 -- -------------------------------------------------------------------------
302 -- PROCEDURE: MIGRATE_WRAPUPS
303 --
304 -- DESCRIPTION:
305 -- This script checks and populates data from JTF_IH_OUTCOME_RESULTS table to
306 -- JTF_IH_WRAP_UPS (if same records weren't found there).
307 --
308 -- HISTORY:
309 -- 03/14/03 ialeshin - Created sql script
310 -- 08/12/03 mpetrosi - Modified changed script into package procedure
311 --
312 -- -------------------------------------------------------------------------
313 PROCEDURE MIGRATE_WRAPUPS AS
314 nCount number;
315 sDummy varchar2(2000);
316 type rWrpUps is record
317 ( Outcome_Id number,
318 Result_Id number,
319 Reason_Id number,
320 Success varchar2(2),
321 Object_Id number,
322 Object_Type varchar2(30),
323 Source_Code_Id number,
324 Source_Code varchar2(30),
325 wrap_up_level varchar2(30),
326 wrap_id number);
327 type tWrpUps is table of rWrpUps index by binary_integer;
328 ttWrpUps tWrpUps;
329 nCnt binary_integer;
330 sReaReq varchar2(1);
331 sResReq varchar2(1);
332 sSql varchar2(2000);
333 iCursor binary_integer;
334 iRes binary_integer;
335 nCampCount number;
336 nSourceCodeID number;
337 nObjectId number;
338 vObjectType varchar2(30);
339 eNoOutcomes exception;
340 eNoOutcomeId exception;
341 eNoResultId exception;
342 eSkip exception;
343
344 type rec_Params is record (
345 name varchar2(30),
346 value varchar2(30));
347 type tbl_Params is table of rec_Params index by binary_integer;
348 v_Params tbl_Params;
349 n_CntParams number;
350 l_param_name varchar2(30);
351 l_param_value number;
352 l_active_flag varchar2(1);
353
354 BEGIN
355
356
357 savepoint jtf_ih_migrate;
358
359 -- If JTF_IH_OUTCOMES_B is empty then raise an exception.
360 select count(*) into nCount from jtf_ih_outcomes_b;
361 if nCount = 0 then
362 raise eNoOutcomes;
363 end if;
364
365 l_active_flag := 'Y';
366
367 --
368 -- Loop over all active outcomes non-campaign based
369 nCnt := 1;
370 For curOut in (select outcome_id, result_required from jtf_ih_outcomes_b where active <> 'N' or active is null ) loop
371 -- add outcome only wrap-up row if no Result is required
372 if curOut.result_required = 'N' or curOut.result_required is null then
373 ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
374 ttWrpUps(nCnt).Result_id := null;
375 ttWrpUps(nCnt).Reason_id := null;
376 ttWrpUps(nCnt).object_id := null;
377 ttWrpUps(nCnt).object_type := null;
378 ttWrpUps(nCnt).source_code_id := null;
379 ttWrpUps(nCnt).source_code := null;
380 ttWrpUps(nCnt).wrap_up_level := 'BOTH';
381 nCnt := nCnt + 1;
382 end if;
383
384 -- get all the valid Outcome-Result pairs where the exists
385 -- in jtf_ih_results_b and is active for the outcome.
386 -- loop through the results to create more wrap-ups for this id
387 for curOutRes in (select outres.result_id, res.result_required
388 from jtf_ih_outcome_results outres, jtf_ih_results_b res
389 where outres.outcome_id = curOut.outcome_id and
390 outres.result_id = res.result_id and
391 (res.active <> 'N' or res.active is null)) loop
392
393 -- if the result does not require a reason,
394 -- then add a Outcome_id, Result_id, null reason_id wrap-up.
395 if (curOutRes.result_required = 'N' or
396 curOutRes.result_required is null) then
397 ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
398 ttWrpUps(nCnt).Result_id := curOutRes.result_id;
399 ttWrpUps(nCnt).Reason_id := null;
400 ttWrpUps(nCnt).object_id := null;
401 ttWrpUps(nCnt).object_type := null;
402 ttWrpUps(nCnt).source_code_id := null;
403 ttWrpUps(nCnt).source_code := null;
404 ttWrpUps(nCnt).wrap_up_level := 'BOTH';
405 nCnt := nCnt + 1;
406 end if;
407
408 -- add all valid active reasons for the current outcome_id
409 -- and result_id that exist in the jtf_ih_reasons_b table
410 for curResRea in (select rr.reason_id
411 from jtf_ih_result_reasons rr, jtf_ih_reasons_b rea
412 where rr.result_id = curOutRes.result_id and
413 rr.reason_id = rea.reason_id and
414 ( rea.active <> 'N' or rea.active is null)) loop
415
416 ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
417 ttWrpUps(nCnt).Result_id := curOutRes.result_id;
418 ttWrpUps(nCnt).Reason_id := curResRea.reason_id;
419 ttWrpUps(nCnt).object_id := null;
420 ttWrpUps(nCnt).object_type := null;
421 ttWrpUps(nCnt).source_code_id := null;
422 ttWrpUps(nCnt).source_code := null;
423 ttWrpUps(nCnt).wrap_up_level := 'BOTH';
424 nCnt := nCnt + 1;
425 end loop; -- end Result-Reason Loop
426
427 end loop; -- end Outcome-Result Loop
428
429 end loop; -- end Outcomes Loop
430
431 -- loop over the campaign based outcomes and build wrap-ups for them
432 for curOut in (select cmpOut.outcome_id, cmpOut.source_code,
433 outc.result_required
434 from jtf_ih_outcomes_b outc, jtf_ih_outcomes_campaigns cmpOut
435 where outc.outcome_id = cmpOut.outcome_id
436 and (outc.active <> 'N' or outc.active is null)
437 order by outc.outcome_id) loop
438 begin
439 -- validate the campaign values and get the source_code_id, object_id
440 -- and object_type values
441 begin
442 SELECT count(*), source_code_id, SOURCE_CODE_FOR_ID, ARC_SOURCE_CODE_FOR
443 INTO nCampCount, nSourceCodeID, nObjectId, vObjectType
444 FROM AMS_SOURCE_CODES
445 WHERE source_code = curOut.source_code
446 AND active_flag = l_active_flag
447 group by source_code_id, SOURCE_CODE_FOR_ID, ARC_SOURCE_CODE_FOR;
448
449 -- if the campaign is not found then skip this outcome,
450 -- the campaign is not valid.
451 if nCampCount = 0 then
452 raise eSkip;
453 end if;
454 exception
455 when no_data_found then
456 raise eSkip;
457 end;
458
459 -- add outcome only wrap-up row for the campaign
460 -- if no Result is required
461 if curOut.result_required = 'N' or curOut.result_required is null then
462 ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
463 ttWrpUps(nCnt).Result_id := null;
464 ttWrpUps(nCnt).Reason_id := null;
465 ttWrpUps(nCnt).object_id := nObjectId;
466 ttWrpUps(nCnt).object_type := vObjectType;
467 ttWrpUps(nCnt).source_code_id := nSourceCodeID;
468 ttWrpUps(nCnt).source_code := curOut.source_code;
469 ttWrpUps(nCnt).wrap_up_level := 'BOTH';
470 nCnt := nCnt + 1;
471 end if;
472
473 -- get all the valid Outcome-Result pairs where the exists
474 -- in jtf_ih_results_b and is active for the outcome.
475 -- loop through the results to create more wrap-ups for this id
476 for curOutRes in (select outr.result_id, res.result_required
477 from jtf_ih_outcome_results outr, jtf_ih_results_b res
478 where outr.outcome_id = curOut.outcome_id and
479 outr.result_id = res.result_id and
480 (res.active <> 'N' or res.active is null)) loop
481
482 -- if the result does not require a reason,
483 -- then add a Outcome_id, Result_id, null reason_id wrap-up.
484 if (curOutRes.result_required = 'N' or
485 curOutRes.result_required is null) then
486 ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
487 ttWrpUps(nCnt).Result_id := curOutRes.result_id;
488 ttWrpUps(nCnt).Reason_id := null;
489 ttWrpUps(nCnt).object_id := nObjectId;
490 ttWrpUps(nCnt).object_type := vObjectType;
491 ttWrpUps(nCnt).source_code_id := nSourceCodeID;
492 ttWrpUps(nCnt).source_code := curOut.source_code;
493 ttWrpUps(nCnt).wrap_up_level := 'BOTH';
494 nCnt := nCnt + 1;
495 end if;
496
497 -- add all valid active reasons for the current outcome_id and
498 -- result_id that exist in the jtf_ih_reasons_b table
499 for curResRea in (select rr.reason_id from jtf_ih_result_reasons rr,
500 jtf_ih_reasons_b rea
501 where rr.result_id = curOutRes.result_id and
502 rr.reason_id = rea.reason_id and
503 (rea.active <> 'N' or rea.active is null)) loop
504 ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
505 ttWrpUps(nCnt).Result_id := curOutRes.result_id;
506 ttWrpUps(nCnt).Reason_id := curResRea.reason_id;
507 ttWrpUps(nCnt).object_id := nObjectId;
508 ttWrpUps(nCnt).object_type := vObjectType;
509 ttWrpUps(nCnt).source_code_id := nSourceCodeID;
510 ttWrpUps(nCnt).source_code := curOut.source_code;
511 ttWrpUps(nCnt).wrap_up_level := 'BOTH';
512 nCnt := nCnt + 1;
513 end loop; -- end Result-Reason Loop
514 end loop; -- end Outcome-Result Loop
515 exception
516 when eSkip then
517 null;
518 end;
519 end loop; -- end Outcomes Loop
520
521 iCursor := dbms_sql.open_cursor;
522 for i in 1..ttWrpUps.count loop
523 --sSql := 'SELECT COUNT(*) FROM jtf_ih_wrap_ups WHERE outcome_id = '||ttWrpUps(i).outcome_Id||' ';
524 v_Params.delete;
525 sSql := 'SELECT COUNT(*) FROM jtf_ih_wrap_ups WHERE outcome_id = :outcome_id ';
526 n_CntParams := 1;
527 v_Params(n_CntParams).name := ':outcome_id';
528 v_Params(n_CntParams).value := to_char(ttWrpUps(i).outcome_Id);
529
530 if ttWrpUps(i).result_id is null then
531 sSql := sSql || 'AND result_id IS NULL ';
532 else
533 -- sSql := sSql || 'AND result_id = '||ttWrpUps(i).result_Id||' ';
534 sSql := sSql || 'AND result_id = :result_id ';
535 n_CntParams := n_CntParams + 1;
536 v_Params(n_CntParams).name := ':result_id';
537 v_Params(n_CntParams).value := to_char(ttWrpUps(i).result_Id);
538 end if;
539
540 if ttWrpUps(i).reason_id is null then
541 sSql := sSql || 'AND reason_id IS NULL ';
542 else
543 --sSql := sSql || 'AND reason_id = '||ttWrpUps(i).reason_Id||' ';
544 sSql := sSql || 'AND reason_id = :reason_id ';
545 n_CntParams := n_CntParams + 1;
546 v_Params(n_CntParams).name := ':reason_id';
547 v_Params(n_CntParams).value := to_char(ttWrpUps(i).reason_Id);
548
549 end if;
550 if ttWrpUps(i).source_code_id is null then
551 sSql := sSql || 'AND source_code_id IS NULL ';
552 else
553 --sSql := sSql || 'AND source_code_id = '||ttWrpUps(i).source_Code_Id||' ';
554 sSql := sSql || 'AND source_code_id = :source_code_id ';
555 n_CntParams := n_CntParams + 1;
556 v_Params(n_CntParams).name := ':source_code_id';
557 v_Params(n_CntParams).value := to_char(ttWrpUps(i).source_Code_Id);
558 end if;
559 if ttWrpUps(i).object_id is null then
560 sSql := sSql || 'AND object_id IS NULL ';
561 else
562 --sSql := sSql || 'AND object_id = '||ttWrpUps(i).Object_Id||' ';
563 sSql := sSql || 'AND object_id = :object_id ';
564 n_CntParams := n_CntParams + 1;
565 v_Params(n_CntParams).name := ':object_id';
566 v_Params(n_CntParams).value := to_char(ttWrpUps(i).Object_Id);
567 end if;
568 if ttWrpUps(i).source_code is null then
569 sSql := sSql || 'AND source_code IS NULL ';
570 else
571 --sSql := sSql || 'AND source_code = '''||ttWrpUps(i).source_Code||''' ';
572 sSql := sSql || 'AND source_code = :source_code ';
573 n_CntParams := n_CntParams + 1;
574 v_Params(n_CntParams).name := ':source_code';
575 v_Params(n_CntParams).value := ttWrpUps(i).source_Code;
576 end if;
577
578 -- Check a JTF_IH_WRAP_UPS about record that has same
579 -- outcome_id, result_id and reason_id
580 --
581 -- dbms_output.put_line(sSql);
582 dbms_sql.parse(iCursor, sSql, dbms_sql.native);
583 FOR i IN 1..v_Params.count LOOP
584 --dbms_output.put_line(i||' '||v_Params(i).name||' '||v_Params(i).value);
585 dbms_sql.bind_variable(iCursor,v_Params(i).name,v_Params(i).value);
586 END LOOP;
587
588 dbms_sql.define_column(iCursor,1, nCount);
589 iRes := dbms_sql.execute(iCursor);
590 if dbms_sql.fetch_rows(iCursor) = 0 then
591 nCount := 0;
592 end if;
593 dbms_sql.column_value(iCursor,1,nCount);
594 -- If nCount is 0 (record not found) then create new one
595 -- in the JTF_IH_WRAP_UPS
596 if nCount = 0 then
597 begin
598 if ttWrpUps(i).result_id is null and
599 ttWrpUps(i).reason_id is not null then
600 raise eSkip;
601 else
602 INSERT INTO jtf_ih_wrap_ups ( WRAP_ID,
603 OUTCOME_ID,
604 RESULT_ID,
605 REASON_ID,
606 SOURCE_CODE_ID,
607 SOURCE_CODE,
608 OBJECT_ID,
609 OBJECT_TYPE,
610 START_DATE,
611 WRAP_UP_LEVEL,
612 CREATED_BY,
613 CREATION_DATE,
614 LAST_UPDATED_BY,
615 LAST_UPDATE_DATE,
616 LAST_UPDATE_LOGIN
617 ) VALUES (
618 jtf_ih_wrap_ups_s1.nextval,
619 ttWrpUps(i).Outcome_Id,
620 ttWrpUps(i).Result_Id,
621 ttWrpUps(i).Reason_Id,
622 ttWrpUps(i).source_code_id,
623 ttWrpUps(i).source_code,
624 ttWrpUps(i).object_id,
625 ttWrpUps(i).object_type,
626 SYSDATE,
627 ttWrpUps(i).wrap_up_level,
628 hz_utility_pub.user_id,
629 SYSDATE,
630 hz_utility_pub.last_update_login,
631 SYSDATE,
632 hz_utility_pub.last_update_login
633 );
634 end if;
635 exception
636 when eSkip then
637 null;
638 when others then
639 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
640 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
641 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
642 end;
643 end if;
644 end loop;
645 dbms_sql.close_cursor(iCursor);
646
647 -- Enh# 3519691
648 UPDATE jtf_ih_outcomes_b SET active='Y' WHERE active IS NULL;
649 UPDATE jtf_ih_results_b SET active='Y' WHERE active IS NULL;
650 UPDATE jtf_ih_reasons_b SET active='Y' WHERE active IS NULL;
651 UPDATE JTF_IH_OUTCOMES_B set RESULT_REQUIRED = 'N' WHERE RESULT_REQUIRED is NULL;
652 UPDATE JTF_IH_RESULTS_B set RESULT_REQUIRED = 'N' WHERE RESULT_REQUIRED is NULL;
653 COMMIT;
654
655 JTF_IH_TOOLS.MIGRATE_IH_WRAPUPS('INTERACTION'); -- Add unique combinations based on jtf_ih_interactions table
656 JTF_IH_TOOLS.MIGRATE_IH_WRAPUPS('ACTIVITY'); -- Add unique combinations based on jtf_ih_activities table
657
658 commit work;
659 exception
660 when eNoOutcomes then
661 --dbms_output.put_line('No Outcomes!');
662 rollback to jtf_ih_migrate;
663 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
664 FND_MESSAGE.SET_TOKEN('ERRORMSG',SQLERRM);
665 FND_MSG_PUB.Add;
666 RETURN;
667 end MIGRATE_WRAPUPS;
668 END JTF_IH_TOOLS;