1 PACKAGE IEM_EMAILACCOUNT_PUB AUTHID CURRENT_USER as
2 /* $Header: iempacts.pls 120.6 2011/12/25 17:35:01 lkullamb ship $ */
3
4 TYPE EMACNT_rec_type IS RECORD (
5 -- SERVER_ID NUMBER,
6 ACCOUNT_NAME VARCHAR2(256) ,
7 db_user varchar2(256),
8 -- DOMAIN VARCHAR2(256) ,
9 ACCOUNT_PASSWORD VARCHAR2(128) ,
10 ACCOUNT_ID NUMBER );
11
12 TYPE EMACNT_tbl_type IS TABLE OF EMACNT_rec_type
13 INDEX BY BINARY_INTEGER;
14 G_emacnt_tbl emacnt_tbl_type;
15
16 TYPE msg_header IS RECORD (
17 msg_id INTEGER,
18 smtp_msg_id VARCHAR2(240),
19 sender_name VARCHAR2(128),
20 received_date DATE,
21 from_str VARCHAR2(80),
22 to_str VARCHAR2(240),
23 priority VARCHAR2(30),
24 replyto VARCHAR2(240),
25 subject VARCHAR2(240),
26 classification VARCHAR2(240),
27 score NUMBER,
28 folder_path VARCHAR2(240)
29 );
30
31 TYPE msg_header_table IS TABLE OF msg_header INDEX BY
32 binary_integer;
33
34 type account_info_record is record (
35 email_user varchar2(80),
36 email_password varchar2(80),
37 domain varchar2(128),
38 db_server_id number,
39 email_account_id number
40 );
41
42 type account_info_table is table of account_info_record index by
43 binary_integer;
44
45 TYPE ACNTDETAILS_rec_type IS RECORD (
46 ACCOUNT_NAME VARCHAR2(256),
47 EMAIL_USER VARCHAR2(100),
48 EMAIL_ADDRESS VARCHAR2(120),
49 REPLY_TO_ADDRESS VARCHAR2(256),
50 FROM_NAME VARCHAR2(100),
51 EMAIL_ACCOUNT_ID NUMBER,
52 SMTP_SERVER VARCHAR2(256),
53 PORT NUMBER,
54 TEMPLATE_CATEGORY_ID NUMBER,
55 SSL_CONNECTION_FLAG VARCHAR2(1) -- added for smtp over SSL
56 );
57
58 TYPE ACNTDETAILS_tbl_type IS TABLE OF ACNTDETAILS_rec_type
59 INDEX BY BINARY_INTEGER;
60
61 TYPE AGNTACNTDETAILS_rec_type IS RECORD (
62 RESOURCE_ID NUMBER,
63 RESOURCE_NAME VARCHAR2(256),
64 USER_NAME VARCHAR2(256),
65 ROLE VARCHAR2(60),
66 LAST_LOGIN_TIME VARCHAR2(256)
67 );
68
69 TYPE AGNTACNTDETAILS_tbl_type IS TABLE OF AGNTACNTDETAILS_rec_type
70 INDEX BY BINARY_INTEGER;
71
72 type AGENTACNT_rec_type is record (
73 agent_account_id number,
74 email_account_id number,
75 account_name varchar2(256),
76 reply_to_address varchar2(256),
77 from_address varchar2(256),
78 from_name varchar2(256),
79 user_name varchar2(256),
80 signature varchar2(256)
81 );
82
83 type AGENTACNT_tbl_type is table of AGENTACNT_rec_type index by
84 binary_integer;
85
86 -- *****************************************************
87 -- Start of Comments
88 -- API name : Get_EmailAccount_List
89 -- Type : Public
90 -- Function : This procedure returns a list of email accounts that a
91 -- particular agent has access to. The agent_id is passed
92 -- in the p_RESOURCE_ID parameter. The returned PL/SQL ta--ble
93 -- has a list of email accounts, their username and login
94 -- passwords and the Group_ID. Any of the email servers that
95 -- belong to this group ID can be used to access the email
96 -- account. An account is available on only one IM server
97 -- the ID of which is the SERVER_ID attribute.
98 -- If the eMC client wants to invoke IM's PLSQL APIs
99 -- it first has to make a JDBC connection to the named IM
100 -- server.
101 -- Pre-reqs : None.
102 -- Parameters :
103 -- IN
104 -- p_api_version_number IN NUMBER Required
105 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
106 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
107 -- p_RESOURCE_ID IN NUMBER,
108 --
109 -- OUT
110 -- x_return_status OUT VARCHAR2
111 -- x_msg_count OUT NUMBER
112 -- x_msg_data OUT VARCHAR2
113 -- x_Email_Acnt_tbl OUT EMACNT_tbl_type
114 --
115 -- Version : 1.0
116 -- Notes :
117 --
118 -- End of comments
119 -- **********************************************************
120
121
122 PROCEDURE Get_EmailAccount_List (p_api_version_number IN NUMBER,
123 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
124 p_commit IN VARCHAR2 := FND_API.G_FALSE,
125 p_RESOURCE_ID IN NUMBER := null,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2,
129 x_Email_Acnt_tbl OUT NOCOPY EMACNT_tbl_type
130 );
131
132 Procedure getEmailHeaders(
133 p_AgentName IN VARCHAR2,
134 p_top_n IN INTEGER default 0,
135 p_top_option IN INTEGER default 1,
136 p_folder_path IN VARCHAR2 default 'ALL',
137 message_headers OUT NOCOPY msg_header_table
138 );
139
140 PROCEDURE ListAgentAccounts (p_api_version_number IN NUMBER,
141 p_init_msg_list IN VARCHAR2 ,
142 p_commit IN VARCHAR2 ,
143 p_RESOURCE_ID IN NUMBER ,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 x_Agent_Acnt_tbl OUT NOCOPY AGENTACNT_tbl_type
148 );
149 -- 12.1.2 Development. Bug 8829918
150 PROCEDURE ListAgentCPAccounts (p_api_version_number IN NUMBER,
151 p_init_msg_list IN VARCHAR2 ,
152 p_commit IN VARCHAR2 ,
153 p_RESOURCE_ID IN NUMBER ,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 x_Agent_Acnt_tbl OUT NOCOPY AGENTACNT_tbl_type
158 );
159 -- 12.1.2 Development. Bug 8829918 Changes end
160
161 PROCEDURE ListAgentAccountDetails (p_api_version_number IN NUMBER,
162 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
163 p_commit IN VARCHAR2 := FND_API.G_FALSE,
164 p_EMAIL_ACCOUNT_ID IN NUMBER,
165 p_ROLEid IN NUMBER:=-1,
166 p_Resource_id IN NUMBER:=-1,
167 p_search_criteria IN VARCHAR2:=null,
168 p_display_size in NUMBER:=null,
169 p_page_count in NUMBER:=null,
170 p_sort_by in VARCHAR2:='F',
171 p_sort_order in NUMBER:=1,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_search_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 x_Agent_Acnt_Dtl_data OUT NOCOPY AGNTACNTDETAILS_tbl_type
177 );
178
179 PROCEDURE ListAccountDetails (p_api_version_number IN NUMBER,
180 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
181 p_commit IN VARCHAR2 := FND_API.G_FALSE,
182 p_email_account_id IN NUMBER := null,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 x_Acnt_Details_tbl OUT NOCOPY ACNTDETAILS_tbl_type
187 );
188
189 END IEM_EmailAccount_PUB;