[Home] [Help]
PACKAGE BODY: APPS.IEM_INBE_PVT
Source
1 PACKAGE BODY IEM_INBE_PVT AS
2 /* $Header: ieminbvb.pls 120.1 2005/10/26 16:34:17 rtripath noship $ */
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
19 CURSOR c_inb_nodes_1 IS
20 select a.from_name, a.email_account_id, c.resource_id
21 from iem_mstemail_accounts a,iem_agents c
22 where a.email_account_id=c.email_account_id
23 and c.resource_id=p_resource_id
24 group by a.from_name,a.email_account_id,c.resource_id
25 order by 1;
26
27 BEGIN
28 IF (FND_PROFILE.VALUE('IEU_QEN_INB_EMAIL') = 'N' ) THEN
29 RETURN;
30 END IF;
31
32 l_node_counter := 0;
33
34 SAVEPOINT start_enumeration;
35
36 l_def_where := ieu_pub.get_enum_res_cat(p_sel_enum_id);
37
38 Select meaning into l_node_label
39 from fnd_lookups
40 where lookup_type = 'IEM_UWQ_EMAIL_LABELS'
41 and lookup_code = 'IEM_INBOUND_EMAIL_LBL';
42
43 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
44 l_tk_list(l_node_counter).VIEW_NAME := 'IEM_INBEMAIL_SUMM_V';
45 l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_INBOUND_EMAIL_SUMM_DS';
46 l_tk_list(l_node_counter).MEDIA_TYPE_ID := 10001;
47 l_tk_list(l_node_counter).WHERE_CLAUSE := '';
48 l_tk_list(l_node_counter).NODE_TYPE := 0;
49 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
50 l_tk_list(l_node_counter).NODE_DEPTH := 1;
51 l_tk_list(l_node_counter).BIND_VARS := '';
52 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
53 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEM_REFINBEMAIL_ACCOUNTS_V';
54 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'Total';
55
56 l_node_counter := l_node_counter + 1;
57
58 --Now build the subnodes
59
60 FOR cur_rec IN c_inb_nodes_1 LOOP
61 l_bind_list(1).bind_var_name := ':resource_id';
62 l_bind_list(1).bind_var_value := p_resource_id;
63 l_bind_list(1).bind_var_data_type := 'NUMBER';
64 l_bind_list(2).bind_var_name := ':ACCOUNT_ID';
65 l_bind_list(2).bind_var_value := cur_rec.email_account_id;
66 l_bind_list(2).bind_var_data_type := 'CHAR';
67
68 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.from_name;
69 l_tk_list(l_node_counter).VIEW_NAME := 'IEM_INBEMAIL_COUNTS_V';
70 -- l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_INBOUND_EMAIL_DS';
71 l_tk_list(l_node_counter).DATA_SOURCE := 'IEM_INBOUND_ACCOUNT_DS';
72 l_tk_list(l_node_counter).MEDIA_TYPE_ID := 10001;
73 l_tk_list(l_node_counter).WHERE_CLAUSE := l_def_where ||'and EMAIL_ACCOUNT_ID = ' || ':ACCOUNT_ID';
74 l_tk_list(l_node_counter).NODE_TYPE := 0;
75 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
76 l_tk_list(l_node_counter).NODE_DEPTH := 2;
77 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_bind_list);
78 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
79 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEM_REFINBEMAIL_ACCOUNTS_V';
80 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'Total';
81
82 l_node_counter := l_node_counter + 1;
83
84 END LOOP;
85
86 IEU_PUB.ADD_UWQ_NODE_DATA
87 (P_RESOURCE_ID,
88 P_SEL_ENUM_ID,
89 l_tk_list
90 );
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 ROLLBACK TO start_enumeration;
95 RAISE;
96
97 END ENUMERATE_INBOUND_NODES;
98
99 END IEM_INBE_PVT;