[Home] [Help]
PACKAGE BODY: APPS.IEM_INBE_PVT
Source
1 PACKAGE BODY IEM_INBE_PVT AS
2 /* $Header: ieminbvb.pls 120.1.12010000.3 2009/08/13 13:34:55 lkullamb ship $ */
3
4 PROCEDURE ENUMERATE_INBOUND_NODES
5 (P_RESOURCE_ID IN NUMBER
6 ,P_LANGUAGE IN VARCHAR2
7 ,P_SOURCE_LANG IN VARCHAR2
8 ,P_SEL_ENUM_ID IN NUMBER
9 )
10 AS
11
12 l_node_counter NUMBER;
13 l_node_pid NUMBER;
14 l_node_label VARCHAR2(100);
15 l_def_where VARCHAR2(20000);
16 l_tk_list IEU_PUB.EnumeratorDataRecordList;
17 l_bind_list IEU_PUB.BindVariableRecordList;
18 p_user_flag varchar2(1);
19 p_acct_flag varchar2(1);
20
21 CURSOR c_inb_nodes_1 IS
22 select a.from_name, a.email_account_id, c.resource_id
23 from iem_mstemail_accounts a,iem_agents c
24 where a.email_account_id=c.email_account_id
25 and c.resource_id=p_resource_id
26 group by a.from_name,a.email_account_id,c.resource_id
27 order by 1;
28
29 BEGIN
30 IF (FND_PROFILE.VALUE('IEU_QEN_INB_EMAIL') = 'N' ) THEN
31 RETURN;
32 END IF;
33
34 l_node_counter := 0;
35
36 SAVEPOINT start_enumeration;
37
38 l_def_where := ieu_pub.get_enum_res_cat(p_sel_enum_id);
39
40 Select meaning into l_node_label
41 from fnd_lookups
42 where lookup_type = 'IEM_UWQ_EMAIL_LABELS'
43 and lookup_code = 'IEM_INBOUND_EMAIL_LBL';
44
45 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
46 l_tk_list(l_node_counter).VIEW_NAME := 'IEM_INBEMAIL_SUMM_V';
47 l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_INBOUND_EMAIL_SUMM_DS';
48 l_tk_list(l_node_counter).MEDIA_TYPE_ID := 10001;
49 l_tk_list(l_node_counter).WHERE_CLAUSE := '';
50 l_tk_list(l_node_counter).NODE_TYPE := 0;
51 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
52 l_tk_list(l_node_counter).NODE_DEPTH := 1;
53 l_tk_list(l_node_counter).BIND_VARS := '';
54 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
55 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEM_REFINBEMAIL_ACCOUNTS_V';
56 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'Total';
57
58 l_node_counter := l_node_counter + 1;
59
60 --Now build the subnodes
61
62 FOR cur_rec IN c_inb_nodes_1 LOOP
63 l_bind_list(1).bind_var_name := ':resource_id';
64 l_bind_list(1).bind_var_value := p_resource_id;
65 l_bind_list(1).bind_var_data_type := 'NUMBER';
66 l_bind_list(2).bind_var_name := ':ACCOUNT_ID';
67 l_bind_list(2).bind_var_value := cur_rec.email_account_id;
68 l_bind_list(2).bind_var_data_type := 'CHAR';
69
70 /* whether cherry pick is enabled at user and account level or not */
71
72 begin
73 /* query to check the cherry pick flag at the user and account level*/
74 select acc.cherry_pick_flag ,agents.cherry_pick_flag
75 into p_acct_flag,p_user_flag
76 from iem_mstemail_accounts acc, iem_agents agents
77 where acc.email_account_id=cur_rec.email_account_id
78 and agents.resource_id =p_resource_id
79 and acc.email_account_id = agents.email_account_id;
80
81 exception when others then
82 p_acct_flag := 'N';
83 p_user_flag := 'N';
84 end;
85
86 If (p_acct_flag = 'Y' and p_user_flag = 'Y') then
87 /* Both user and account cherry pick enabled */
88 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.from_name;
89 l_tk_list(l_node_counter).VIEW_NAME := 'IEM_QUEUEEMAIL_CHERRYPICK_v';
90 l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_QUEUEEMAIL_CHERRYPICK_DS';
91 l_tk_list(l_node_counter).MEDIA_TYPE_ID := 10001;
92 l_tk_list(l_node_counter).WHERE_CLAUSE := l_def_where ||'and EMAIL_ACCOUNT_ID =' ||':ACCOUNT_ID';
93 l_tk_list(l_node_counter).NODE_TYPE := 0;
94 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
95 l_tk_list(l_node_counter).NODE_DEPTH := 2;
96 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_bind_list);
97 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
98 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEM_REFINBEMAIL_ACCOUNTS_V';
99 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'Total';
100
101 l_node_counter := l_node_counter + 1;
102
103 else
104 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.from_name;
105 l_tk_list(l_node_counter).VIEW_NAME := 'IEM_INBEMAIL_COUNTS_V';
106 -- l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_INBOUND_EMAIL_DS';
107 l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_INBOUND_ACCOUNT_DS';
108 l_tk_list(l_node_counter).MEDIA_TYPE_ID := 10001;
109 l_tk_list(l_node_counter).WHERE_CLAUSE := l_def_where ||'and EMAIL_ACCOUNT_ID = ' || ':ACCOUNT_ID';
110 l_tk_list(l_node_counter).NODE_TYPE := 0;
111 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
112 l_tk_list(l_node_counter).NODE_DEPTH := 2;
113 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_bind_list);
114 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
115 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEM_REFINBEMAIL_ACCOUNTS_V';
116 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'Total';
117
118 l_node_counter := l_node_counter + 1;
119 end if;
120
121 END LOOP;
122
123 IEU_PUB.ADD_UWQ_NODE_DATA
124 (P_RESOURCE_ID,
125 P_SEL_ENUM_ID,
126 l_tk_list
127 );
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 ROLLBACK TO start_enumeration;
132 RAISE;
133
134 END ENUMERATE_INBOUND_NODES;
135
136 END IEM_INBE_PVT;