eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEM_THEMES_PVT dependencies on IEM_THEMES

Line 1: PACKAGE BODY IEM_THEMES_PVT as

1: PACKAGE BODY IEM_THEMES_PVT as
2: /* $Header: iemptheb.pls 115.14 2003/08/26 23:42:00 sboorela shipped $*/
3: /* Fixed Bug 1339163 kbeagle on 11/29/00 Dup theme error when updating score */
4: /* 08/14/01 chtang added create_item_wrap_sss() for 11.5.6 */
5: /* 05/07/02 chtang added update last_update_date of keyword in calculate_weight */

Line 8: G_PKG_NAME CONSTANT varchar2(30) :='IEM_THEMES_PVT ';

4: /* 08/14/01 chtang added create_item_wrap_sss() for 11.5.6 */
5: /* 05/07/02 chtang added update last_update_date of keyword in calculate_weight */
6: /* 10/15/02 chtang added update last_update_date of intent in calculate_weight */
7: /*****************************************************************************/
8: G_PKG_NAME CONSTANT varchar2(30) :='IEM_THEMES_PVT ';
9:
10:
11: PROCEDURE create_item (p_api_version_number IN NUMBER,
12: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,

Line 54: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id

50: -- Initialize API return status to SUCCESS
51: x_return_status := FND_API.G_RET_STS_SUCCESS;
52:
53: -- Take this out. Handle duplicates in the exception block.
54: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
55: and theme=p_theme and query_response=p_query_response;
56:
57: IF l_cnt=0 THEN
58: SELECT iem_themes_s1.nextval

Line 58: SELECT iem_themes_s1.nextval

54: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
55: and theme=p_theme and query_response=p_query_response;
56:
57: IF l_cnt=0 THEN
58: SELECT iem_themes_s1.nextval
59: INTO l_seq_id
60: FROM dual;
61: INSERT INTO iem_themes (theme_id,
62: classification_id,

Line 61: INSERT INTO iem_themes (theme_id,

57: IF l_cnt=0 THEN
58: SELECT iem_themes_s1.nextval
59: INTO l_seq_id
60: FROM dual;
61: INSERT INTO iem_themes (theme_id,
62: classification_id,
63: theme,
64: score,
65: query_response,

Line 149: DELETE FROM IEM_THEMES WHERE THEME_ID = p_theme_id;

145: END IF;
146: -- Initialize API return status to SUCCESS
147: x_return_status := FND_API.G_RET_STS_SUCCESS;
148:
149: DELETE FROM IEM_THEMES WHERE THEME_ID = p_theme_id;
150:
151: -- Standard Check Of p_commit.
152: IF FND_API.To_Boolean(p_commit) THEN
153: COMMIT WORK;

Line 255: update IEM_THEMES SET

251: END IF;
252: -- Initialize API return status to SUCCESS
253: x_return_status := FND_API.G_RET_STS_SUCCESS;
254:
255: update IEM_THEMES SET
256: classification_id = decode( p_classification_id, FND_API.G_MISS_NUM,null,null, classification_id, p_classification_id),
257: theme = decode(p_theme,FND_API.G_MISS_CHAR,null,null,theme,p_theme),
258: score = decode(p_score,FND_API.G_MISS_CHAR,null,null,score,p_score),
259: query_response = decode( p_query_response, FND_API.G_MISS_CHAR,null,null, query_response, p_query_response),

Line 379: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id

375:
376: l_theme2 := rtrim(ltrim(l_theme, ' '), ' ');
377:
378: -- Take this out. Handle duplicates in the exception block.
379: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
380: and theme=p_theme and query_response=p_query_response;
381:
382: IF (l_cnt > 0 ) then
383: raise IEM_DUPLICATE_THEME;

Line 386: IEM_THEMES_PVT.create_item(

382: IF (l_cnt > 0 ) then
383: raise IEM_DUPLICATE_THEME;
384: end if;
385:
386: IEM_THEMES_PVT.create_item(
387: p_api_version_number =>p_api_version_number,
388: p_init_msg_list => p_init_msg_list,
389: p_commit => p_commit,
390: p_score => p_score,

Line 510: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id

506:
507: l_theme2 := rtrim(ltrim(l_theme, ' '), ' ');
508:
509: -- Take this out. Handle duplicates in the exception block.
510: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
511: and theme=p_theme and query_response=p_query_response;
512:
513: IF (l_cnt > 0 ) then
514: raise IEM_DUPLICATE_THEME;

Line 517: IEM_THEMES_PVT.create_item(

513: IF (l_cnt > 0 ) then
514: raise IEM_DUPLICATE_THEME;
515: end if;
516:
517: IEM_THEMES_PVT.create_item(
518: p_api_version_number =>p_api_version_number,
519: p_init_msg_list => p_init_msg_list,
520: p_commit => p_commit,
521: p_score => p_score,

Line 633: FROM IEM_THEMES

629:
630: --Actual API starts here
631: FORALL i IN p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST
632: DELETE
633: FROM IEM_THEMES
634: WHERE theme_id = p_thes_ids_tbl(i);
635:
636: IF SQL%NOTFOUND THEN
637: FND_MESSAGE.SET_NAME('IEM', 'IEM_EXP_INVALID_ACCOUNT');

Line 725: from iem_themes

721:
722: --Actual API starts here
723: FOR j in p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST LOOP
724: select classification_id into l_class_id
725: from iem_themes
726: where theme_id=p_thes_ids_tbl(j);
727: EXIT;
728: END LOOP;
729: FORALL i IN p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST

Line 731: FROM IEM_THEMES

727: EXIT;
728: END LOOP;
729: FORALL i IN p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST
730: DELETE
731: FROM IEM_THEMES
732: WHERE theme_id = p_thes_ids_tbl(i);
733: -- Score Readjustment using noise reduction algorithim
734: delete from iem_theme_docs where theme_id not in
735: (select theme_id from iem_themes);

Line 735: (select theme_id from iem_themes);

731: FROM IEM_THEMES
732: WHERE theme_id = p_thes_ids_tbl(i);
733: -- Score Readjustment using noise reduction algorithim
734: delete from iem_theme_docs where theme_id not in
735: (select theme_id from iem_themes);
736: delete from iem_account_intent_docs where account_intent_doc_id
737: not in (select account_intent_doc_id from iem_theme_docs);
738: select email_account_id into l_email_account_id
739: from iem_classifications

Line 741: iem_themes_pvt.calculate_weight (l_email_account_id,

737: not in (select account_intent_doc_id from iem_theme_docs);
738: select email_account_id into l_email_account_id
739: from iem_classifications
740: where classification_id=l_class_id;
741: iem_themes_pvt.calculate_weight (l_email_account_id,
742: 'Q' ,
743: l_status );
744: iem_themes_pvt.calculate_weight (l_email_account_id,
745: 'R' ,

Line 744: iem_themes_pvt.calculate_weight (l_email_account_id,

740: where classification_id=l_class_id;
741: iem_themes_pvt.calculate_weight (l_email_account_id,
742: 'Q' ,
743: l_status );
744: iem_themes_pvt.calculate_weight (l_email_account_id,
745: 'R' ,
746: l_status );
747:
748: IF SQL%NOTFOUND THEN

Line 848: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id and theme=l_theme2 and query_response=p_query_response and NOT theme_id=p_theme_id;

844: l_theme2 := rtrim(ltrim(l_theme, ' '), ' ');
845:
846: -- kbeagle 12-29-00 Added 'and NOT theme_id=p_theme_id' fix for bug 1339163
847:
848: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id and theme=l_theme2 and query_response=p_query_response and NOT theme_id=p_theme_id;
849: IF (l_cnt > 0 ) then
850: raise IEM_DUPLICATE_THEME;
851: end if;
852: IEM_THEMES_PVT.update_item(

Line 852: IEM_THEMES_PVT.update_item(

848: SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id and theme=l_theme2 and query_response=p_query_response and NOT theme_id=p_theme_id;
849: IF (l_cnt > 0 ) then
850: raise IEM_DUPLICATE_THEME;
851: end if;
852: IEM_THEMES_PVT.update_item(
853: p_api_version_number =>p_api_version_number,
854: p_init_msg_list => p_init_msg_list,
855: p_commit => p_commit,
856: p_theme_id => p_theme_id,

Line 964: from iem_themes

960: BEGIN
961: x_return_status:='S';
962:
963: select nvl(sum(doc_count),0)+1 into l_cnt
964: from iem_themes
965: where query_response=p_query_response
966: and classification_id=p_classification_id
967: and theme=p_theme;
968: select email_account_id into l_email_account_id

Line 972: SELECT iem_themes_s1.nextval

968: select email_account_id into l_email_account_id
969: from iem_classifications
970: where classification_id=p_classification_id;
971: IF l_cnt=1 THEN
972: SELECT iem_themes_s1.nextval
973: INTO l_seq_id
974: FROM dual;
975: INSERT INTO iem_themes (theme_id,
976: classification_id,

Line 975: INSERT INTO iem_themes (theme_id,

971: IF l_cnt=1 THEN
972: SELECT iem_themes_s1.nextval
973: INTO l_seq_id
974: FROM dual;
975: INSERT INTO iem_themes (theme_id,
976: classification_id,
977: theme,
978: score,
979: query_response,

Line 1005: update iem_themes

1001: IF l_status='E' THEN
1002: raise DOC_EXCEP;
1003: END IF;
1004: ELSE
1005: update iem_themes
1006: set doc_count=l_cnt
1007: where query_response=p_query_response
1008: and classification_id=p_classification_id
1009: and theme=p_theme;

Line 1011: from iem_themes

1007: where query_response=p_query_response
1008: and classification_id=p_classification_id
1009: and theme=p_theme;
1010: select theme_id into l_theme_id
1011: from iem_themes
1012: where query_response=p_query_response
1013: and classification_id=p_classification_id
1014: and theme=p_theme;
1015: IEM_THEME_DOCS_PVT.create_item(p_account_intent_doc_id=>p_doc_seq_no ,

Line 1023: iem_themes_pvt.calculate_weight (l_email_account_id,

1019: raise DOC_EXCEP;
1020: END IF;
1021: END IF;
1022: -- Recalculation of Theme Weight
1023: iem_themes_pvt.calculate_weight (l_email_account_id,
1024: p_query_response ,
1025: l_status );
1026: x_return_status:=l_status;
1027: EXCEPTION WHEN DOC_EXCEP THEN

Line 1048: from iem_themes a,iem_classifications b

1044: l_class_id number;
1045: CURSOR c_theme is
1046: select a.theme_id,a.classification_id,
1047: a.theme,a.query_response,a.score
1048: from iem_themes a,iem_classifications b
1049: where a.classification_id=b.classification_id
1050: and b.email_account_id=p_email_account_id
1051: and a.query_response=p_query_response;
1052:

Line 1054: from iem_themes a,iem_classifications b

1050: and b.email_account_id=p_email_account_id
1051: and a.query_response=p_query_response;
1052:
1053: cursor c1 is select a.classification_id,sum(power(a.score,2)) score
1054: from iem_themes a,iem_classifications b
1055: where a.classification_id=b.classification_id
1056: and b.email_account_id=p_email_account_id
1057: and a.query_response=p_query_response
1058: group by a.classification_id;

Line 1061: from iem_themes a,iem_classifications b

1057: and a.query_response=p_query_response
1058: group by a.classification_id;
1059:
1060: cursor c_calc is select a.theme_id,a.score
1061: from iem_themes a,iem_classifications b
1062: where a.classification_id=b.classification_id
1063: and b.email_account_id=p_email_account_id
1064: and a.query_response=p_query_response
1065: and a.classification_id=l_class_id;

Line 1091: from iem_themes A,iem_classifications B

1087:
1088: -- no of document matching the theme in the system
1089:
1090: select nvl(sum(a.doc_count),0) into l_Nt
1091: from iem_themes A,iem_classifications B
1092: where A.classification_id=B.classification_id
1093: and B.email_account_id=p_email_account_id
1094: and A.query_response=v1.query_response
1095: and A.theme=v1.theme;

Line 1100: from iem_themes

1096:
1097: -- no of document matching the theme in the bin
1098:
1099: select nvl(sum(doc_count),0) into l_Rt
1100: from iem_themes
1101: where query_response=v1.query_response
1102: and classification_id=v1.classification_id
1103: and theme=v1.theme;
1104:

Line 1108: update iem_themes

1104:
1105: -- l_temp:=((l_Rt+0.5)/(l_Nt-l_Rt+0.5))*((l_N+.5)/(l_R+.5));
1106: l_temp:=((l_Rt+0.5)/(l_Nt-l_Rt+0.5))*((l_N-l_Nt-l_R+l_Rt+0.5)/(l_R-l_Rt+0.5));
1107: l_weight:=round(log(10,l_temp),2);
1108: update iem_themes
1109: set score=l_weight
1110: where theme_id=v1.theme_id;
1111: END LOOP;
1112: -- Normalised the score using RMS

Line 1119: update iem_themes

1115: update iem_classifications
1116: set last_update_date=sysdate
1117: where classification_id=v1.classification_id;
1118: for v2 in c_calc loop
1119: update iem_themes
1120: set score=round(v2.score/sqrt(v1.score),2),
1121: last_update_date=sysdate
1122: where theme_id=v2.theme_id;
1123: end loop;

Line 1129: END IEM_THEMES_PVT;

1125: -- End Of Normalised the score using RMS
1126: EXCEPTION WHEN OTHERS THEN
1127: x_return_status:='E';
1128: END;
1129: END IEM_THEMES_PVT;