1 PACKAGE IEM_EMAILPROC_PVT AUTHID CURRENT_USER AS
2 /* $Header: iemvruls.pls 115.6 2003/08/19 19:37:39 liangxia noship $ */
3 --
4 --
5 -- Purpose: Mantain Email Processing Rules Engine related operations
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 8/1/2002 Created
10 -- Liang Xia 11/15/2002 Added dynamic Classification
11 -- Fixed NOCOPY, FND_API.G_MISS.. GSCC warning
12 -- Liang Xia 12/04/2002 Completely fixed NOCOPY FND_API.G_MISS GSCC warning
13 -- Liang Xia 02/11/2003 Fixed bug2797418:invalid object because of spec-body miss match
14 -- Liang Xia 06/10/2003 Added Document Retrieval Rule type
15 -- Liang Xia 08/11/2003 Added Auto-Redirect Rule type
16 -- --------- ------ ------------------------------------------
17 G_PKG_NAME VARCHAR2(256) := 'IEM_EMAILPROC_PVT';
18 G_EMAILPROC_ID varchar2(30) ;
19 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
20
21 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
22
23 TYPE emailProc_rec is RECORD (
24 emailProc_id number,
25 Name varchar2(256),
26 description varchar2(256),
27 type varchar2(30), --STATIC or DYNAMIC
28 rule_type varchar2(30),
29 created_by varchar2(256),
30 action varchar2(30),
31 creation_date varchar2(50)
32 );
33
34 --Table of emailProc_rec
35 TYPE emailProc_tbl is TABLE OF emailProc_rec INDEX BY BINARY_INTEGER;
36
37 TYPE acctEmailProc_rec is RECORD (
38 Account_emailProc_id number,
39 emailProc_id number,
40 name varchar2(256),
41 description varchar2(256),
42 type varchar2(30), --STATIC or DYNAMIC
43 rule_type varchar2(30),
44 action varchar2(30),
45 priority number,
46 enabled_flag varchar2(1)
47 );
48
49 --Table of acctEmailProc_rec
50 TYPE acctEmailProc_tbl is TABLE OF acctEmailProc_rec INDEX BY BINARY_INTEGER;
51
52
53 -- Start of Comments
54 -- API name : loadEmailProc
55 -- Type : Private
56 -- Function : This procedure load all email processing rules
57 -- Pre-reqs : None.
58 -- Parameters :
59 PROCEDURE loadEmailProc (
60 p_api_version_number IN NUMBER,
61 p_init_msg_list IN VARCHAR2 := null,
62 p_commit IN VARCHAR2 := null,
63 x_classification OUT NOCOPY emailProc_tbl,
64 x_autoDelete OUT NOCOPY emailProc_tbl,
65 x_autoAck OUT NOCOPY emailProc_tbl,
66 x_autoProc OUT NOCOPY emailProc_tbl,
67 x_redirect OUT NOCOPY emailProc_tbl,
68 x_3Rs OUT NOCOPY emailProc_tbl,
69 x_document OUT NOCOPY emailProc_tbl,
70 x_route OUT NOCOPY emailProc_tbl,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2
74 );
75
76 PROCEDURE loadAcctEmailProc (
77 p_api_version_number IN NUMBER,
78 p_init_msg_list IN VARCHAR2 := null,
79 p_commit IN VARCHAR2 := null,
80 p_acct_id IN NUMBER,
81 x_classification OUT NOCOPY acctEmailProc_tbl,
82 x_autoDelete OUT NOCOPY acctEmailProc_tbl,
83 x_autoAck OUT NOCOPY acctEmailProc_tbl,
84 x_autoProc OUT NOCOPY acctEmailProc_tbl,
85 x_redirect OUT NOCOPY acctEmailProc_tbl,
86 x_3Rs OUT NOCOPY acctEmailProc_tbl,
87 x_document OUT NOCOPY acctEmailProc_tbl,
88 x_route OUT NOCOPY acctEmailProc_tbl,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2
92 );
93
94 PROCEDURE deleteAcctEmailProc (
95 p_api_version_number IN NUMBER,
96 p_init_msg_list IN VARCHAR2 := null,
97 p_commit IN VARCHAR2 := null,
98 p_acct_id IN NUMBER,
99 p_rule_type In VARCHAR2,
100 p_emailProc_id IN NUMBER,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2
104 );
105
106 --update iem_routes, update iem_route_rules, insert iem_route_rules
107 PROCEDURE update_emailproc_wrap (
108 p_api_version_number IN NUMBER,
109 p_init_msg_list IN VARCHAR2 := null,
110 p_commit IN VARCHAR2 := null,
111 p_emailproc_id IN NUMBER,
112 p_name IN VARCHAR2:= null,
113 p_ruling_chain IN VARCHAR2:= null,
114 p_description IN VARCHAR2:= null,
115 p_all_email IN VARCHAR2:= null,
116 p_rule_type IN VARCHAR2:= null,
117
118 --below is the data for update
119 p_update_rule_ids_tbl IN jtf_varchar2_Table_100,
120 p_update_rule_keys_tbl IN jtf_varchar2_Table_100,
121 p_update_rule_operators_tbl IN jtf_varchar2_Table_100,
122 p_update_rule_values_tbl IN jtf_varchar2_Table_300,
123 --below is the data for insert
124 p_new_rule_keys_tbl IN jtf_varchar2_Table_100,
125 p_new_rule_operators_tbl IN jtf_varchar2_Table_100,
126 p_new_rule_values_tbl IN jtf_varchar2_Table_300,
127 --below is the data to be removed
128 p_remove_rule_ids_tbl IN jtf_varchar2_Table_100,
129 --below is the action and action parameter to be updated
130 p_action IN VARCHAR2 :=null,
131 p_parameter1_tbl IN jtf_varchar2_Table_300,
132 p_parameter2_tbl IN jtf_varchar2_Table_300,
133 p_parameter_tag_tbl IN jtf_varchar2_Table_100,
134
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2 );
138
139 -- ***************************************************************************
140 -- Start of Comments
141 -- API name : create_emailproc_wrap
142 -- Type : Private
143 -- Function : This procedure is a wrap function to create Email Processing involved
144 -- inserting tuple in iem_emailprocs table and iem_emailproc_rules table
145 -- Pre-reqs : None.
146 -- End of comments
147 -- ***********************************************************************
148 PROCEDURE create_emailproc_wrap (
149 p_api_version_number IN NUMBER,
150 p_init_msg_list IN VARCHAR2 := null,
151 p_commit IN VARCHAR2 := null,
152 p_route_name IN VARCHAR2,
153 p_route_description IN VARCHAR2:= null,
154 p_route_boolean_type_code IN VARCHAR2,
155 p_rule_type IN VARCHAR2,
156 p_action IN VARCHAR2,
157 p_all_email IN VARCHAR2,
158 p_rule_key_typecode_tbl IN jtf_varchar2_Table_100 ,
159 p_rule_operator_typecode_tbl IN jtf_varchar2_Table_100,
160 p_rule_value_tbl IN jtf_varchar2_Table_300,
161 p_parameter1_tbl IN jtf_varchar2_Table_300 ,
162 p_parameter2_tbl IN jtf_varchar2_Table_300 ,
163 p_parameter_tag_tbl IN jtf_varchar2_Table_100 ,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2 );
167
168 -- ************************************************************************
169 -- Start of Comments
170 -- API name : create_wrap_account_emailprocs
171 -- Type : Private
172 -- Function : This procedure is a wrap function to create record in iem_account_emailprocs table
173 -- Pre-reqs : None.
174 -- Parameters :
175 -- End of comments
176 -- *********************************************************************************************
177 PROCEDURE create_wrap_account_emailprocs (
178 p_api_version_number IN NUMBER,
179 p_init_msg_list IN VARCHAR2 := null,
180 p_commit IN VARCHAR2 := null,
181 p_email_account_id IN NUMBER,
182 p_emailproc_id IN NUMBER,
183 p_enabled_flag IN VARCHAR2,
184 p_priority IN NUMBER,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2
188 );
189
190 -- ************************************************************************
191 -- Start of Comments
192 -- API name : update_wrap_account_emailprocs
193 -- Type : Private
194 -- Function : This procedure is a wrap function to update record in iem_account_emailprocs
195 -- Pre-reqs : None.
196 -- Parameters :
197 -- End of comments
198
199 -- *********************************************************************************************
200 PROCEDURE update_wrap_account_emailprocs (
201 p_api_version_number IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := null,
203 p_commit IN VARCHAR2 := null,
204 p_email_account_id IN NUMBER,
205 p_emailproc_ids_tbl IN jtf_varchar2_Table_100,
206 p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
207 p_delete_emailproc_ids_tbl IN jtf_varchar2_Table_100,
208 p_rule_type IN varchar2,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2
212 ) ;
213
214
215 -- Start of Comments
216 -- API name : delete_item_emailproc
217 -- Type : Private
218 -- Function : This procedure delete a batch of records in the table IEM_EMAILPROCS
219 -- Pre-reqs : None.
220 -- Parameters :
221 -- End of comments
222 -- ***************************************************************************
223 PROCEDURE delete_item_emailproc
224 (p_api_version_number IN NUMBER,
225 P_init_msg_list IN VARCHAR2 := null,
226 p_commit IN VARCHAR2 := null,
227 p_emailproc_id IN NUMBER,
228 p_rule_type IN VARCHAR2,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2);
232
233
234 PROCEDURE delete_acct_emailproc_by_acct
235 (p_api_version_number IN NUMBER,
236 P_init_msg_list IN VARCHAR2 := null,
237 p_commit IN VARCHAR2 := null,
238 p_email_account_id IN NUMBER,
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_msg_count OUT NOCOPY NUMBER,
241 x_msg_data OUT NOCOPY VARCHAR2);
242
243 END IEM_EMAILPROC_PVT;