1 PACKAGE IEM_EMAILPROC_PVT AUTHID CURRENT_USER AS
2 /* $Header: iemvruls.pls 120.0.12010000.2 2009/07/13 04:14:14 lkullamb ship $ */
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_parameter3_tbl IN jtf_varchar2_Table_300,
134 p_parameter_tag_tbl IN jtf_varchar2_Table_100,
135
136 x_return_status OUT NOCOPY VARCHAR2,
137 x_msg_count OUT NOCOPY NUMBER,
138 x_msg_data OUT NOCOPY VARCHAR2 );
139
140 -- ***************************************************************************
141 -- Start of Comments
142 -- API name : create_emailproc_wrap
143 -- Type : Private
144 -- Function : This procedure is a wrap function to create Email Processing involved
145 -- inserting tuple in iem_emailprocs table and iem_emailproc_rules table
146 -- Pre-reqs : None.
147 -- End of comments
148 -- ***********************************************************************
149 PROCEDURE create_emailproc_wrap (
150 p_api_version_number IN NUMBER,
151 p_init_msg_list IN VARCHAR2 := null,
152 p_commit IN VARCHAR2 := null,
153 p_route_name IN VARCHAR2,
154 p_route_description IN VARCHAR2:= null,
155 p_route_boolean_type_code IN VARCHAR2,
156 p_rule_type IN VARCHAR2,
157 p_action IN VARCHAR2,
158 p_all_email IN VARCHAR2,
159 p_rule_key_typecode_tbl IN jtf_varchar2_Table_100 ,
160 p_rule_operator_typecode_tbl IN jtf_varchar2_Table_100,
161 p_rule_value_tbl IN jtf_varchar2_Table_300,
162 p_parameter1_tbl IN jtf_varchar2_Table_300 ,
163 p_parameter2_tbl IN jtf_varchar2_Table_300 ,
164 p_parameter3_tbl IN jtf_varchar2_Table_300 ,
165 p_parameter_tag_tbl IN jtf_varchar2_Table_100 ,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2 );
169
170 -- ************************************************************************
171 -- Start of Comments
172 -- API name : create_wrap_account_emailprocs
173 -- Type : Private
174 -- Function : This procedure is a wrap function to create record in iem_account_emailprocs table
175 -- Pre-reqs : None.
176 -- Parameters :
177 -- End of comments
178 -- *********************************************************************************************
179 PROCEDURE create_wrap_account_emailprocs (
180 p_api_version_number IN NUMBER,
181 p_init_msg_list IN VARCHAR2 := null,
182 p_commit IN VARCHAR2 := null,
183 p_email_account_id IN NUMBER,
184 p_emailproc_id IN NUMBER,
185 p_enabled_flag IN VARCHAR2,
186 p_priority IN NUMBER,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2
190 );
191
192 -- ************************************************************************
193 -- Start of Comments
194 -- API name : update_wrap_account_emailprocs
195 -- Type : Private
196 -- Function : This procedure is a wrap function to update record in iem_account_emailprocs
197 -- Pre-reqs : None.
198 -- Parameters :
199 -- End of comments
200
201 -- *********************************************************************************************
202 PROCEDURE update_wrap_account_emailprocs (
203 p_api_version_number IN NUMBER,
204 p_init_msg_list IN VARCHAR2 := null,
205 p_commit IN VARCHAR2 := null,
206 p_email_account_id IN NUMBER,
207 p_emailproc_ids_tbl IN jtf_varchar2_Table_100,
208 p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
209 p_delete_emailproc_ids_tbl IN jtf_varchar2_Table_100,
210 p_rule_type IN varchar2,
211 x_return_status OUT NOCOPY VARCHAR2,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2
214 ) ;
215
216
217 -- Start of Comments
218 -- API name : delete_item_emailproc
219 -- Type : Private
220 -- Function : This procedure delete a batch of records in the table IEM_EMAILPROCS
221 -- Pre-reqs : None.
222 -- Parameters :
223 -- End of comments
224 -- ***************************************************************************
225 PROCEDURE delete_item_emailproc
226 (p_api_version_number IN NUMBER,
227 P_init_msg_list IN VARCHAR2 := null,
228 p_commit IN VARCHAR2 := null,
229 p_emailproc_id IN NUMBER,
230 p_rule_type IN VARCHAR2,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2);
234
235
236 PROCEDURE delete_acct_emailproc_by_acct
237 (p_api_version_number IN NUMBER,
238 P_init_msg_list IN VARCHAR2 := null,
239 p_commit IN VARCHAR2 := null,
240 p_email_account_id IN NUMBER,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_msg_count OUT NOCOPY NUMBER,
243 x_msg_data OUT NOCOPY VARCHAR2);
244
245 END IEM_EMAILPROC_PVT;
[Home] [Help]