1 PACKAGE IEM_EMAIL_PROC_PVT AUTHID CURRENT_USER as
2 /* $Header: iemmprps.pls 120.10 2012/01/05 22:25:48 siahmed ship $ */
3
4 TYPE email_data_type IS RECORD (
5 key varchar2(100),
6 value varchar2(500));
7 TYPE email_data_tbl IS TABLE OF email_data_type
8 INDEX BY BINARY_INTEGER;
9 TYPE email_doc_type IS RECORD (
10 doc_id number, --Document Id to be send
11 type varchar2(1)); -- Sending type I-Insert,A-Attach
12 TYPE email_doc_tbl IS TABLE OF email_doc_type
13 INDEX BY BINARY_INTEGER;
14 TYPE t_number_table IS TABLE OF NUMBER;
15 PROCEDURE PROC_EMAILS(ERRBUF OUT NOCOPY VARCHAR2,
16 ERRRET OUT NOCOPY VARCHAR2,
17 p_api_version_number in number:= 1.0,
18 p_init_msg_list IN VARCHAR2 ,
19 p_commit IN VARCHAR2 ,
20 p_count IN NUMBER
21 ) ;
22 PROCEDURE iem_logger( l_logmessage in varchar2);
23 PROCEDURE iem_returned_msg_rec(x_msg_rec out nocopy iem_rt_preproc_emails%ROWTYPE);
24
25 PROCEDURE IEM_CLASSIFICATION_PROC(
26 p_email_account_id in number,
27 p_keyval in iem_route_pub.keyVals_tbl_type,
28 x_rt_classification_id out nocopy number,
29 x_status out nocopy varchar2,
30 x_out_text out nocopy varchar2);
31
32 PROCEDURE IEM_ROUTING_PROC(
33 p_email_account_id in number,
34 p_keyval in iem_route_pub.keyVals_tbl_type,
35 x_routing_group_id out nocopy number,
36 x_status out nocopy varchar2,
37 x_out_text out nocopy varchar2);
38 PROCEDURE IEM_INVOKE_WORKFLOW(p_message_id in number,
39 p_source_message_id in number,
40 p_message_size in number,
41 p_sender_name in varchar2,
42 p_user_name in varchar2,
43 p_domain_name in varchar2,
44 p_priority in varchar2,
45 p_message_status in varchar2,
46 p_email_account_id in number,
47 x_wfoutval out nocopy varchar2,
48 x_status out nocopy varchar2,
49 x_out_text out nocopy varchar2);
50 PROCEDURE IEM_GET_MERGEVAL(p_email_account_id in number,
51 p_mailer in varchar2,
52 p_dflt_sender in varchar2,
53 p_subject in varchar2,
54 x_qual_tbl out nocopy IEM_OUTBOX_PROC_PUB.QualifierRecordList,
55 x_status out nocopy varchar2,
56 x_out_text out nocopy varchar2);
57 PROCEDURE IEM_AUTOACK(p_email_user in varchar2,
58 p_mailer in varchar2,
59 p_sender in varchar2,
60 p_subject in varchar2,
61 p_domain_name in varchar2,
62 p_document_id in number,
63 p_dflt_sender in varchar2,
64 p_int_id in number,
65 p_master_account_id in number,
66 x_status OUT NOCOPY varchar2,
67 x_out_text OUT NOCOPY varchar2);
68
69
70 PROCEDURE IEM_SRSTATUS_UPDATE(p_sr_id in number,
71 p_status_id in number,
72 p_email_rec in iem_rt_preproc_emails%rowtype,
73 x_status out nocopy varchar2,
74 x_out_text out nocopy varchar2);
75
76 PROCEDURE IEM_PROC_IH(
77 p_type in varchar2, -- MEDIA/ACTIVITY/MLCS/INTERACTION
78 p_action in varchar2, -- ADD/UPDATE/CLOSE
79 p_interaction_rec in JTF_IH_PUB.interaction_rec_type,
80 p_activity_rec in JTF_IH_PUB.activity_rec_type,
81 p_media_lc_rec in JTF_IH_PUB.media_lc_rec_type,
82 p_media_rec in JTF_IH_PUB.media_rec_type,
83 x_id OUT NOCOPY NUMBER,
84 x_status out nocopy varchar2,
85 x_out_text out nocopy varchar2);
86
87 PROCEDURE IEM_WRAPUP(p_interaction_id in number,
88 p_media_id in number,
89 p_milcs_id in number,
90 p_action in varchar2,
91 p_email_rec in iem_rt_preproc_emails%rowtype,
92 p_action_id in number,
93 x_out_text out NOCOPY varchar2,
94 x_status out nocopy varchar2);
95
96 PROCEDURE IEM_AUTOREPLY(p_interaction_id in number,
97 p_media_id in number,
98 p_post_rec in iem_rt_preproc_emails%rowtype,
99 p_doc_tbl in email_doc_tbl,
100 p_subject in varchar2,
101 P_TAG_KEY_VALUE_TBL in IEM_OUTBOX_PROC_PUB.keyVals_tbl_type,
102 P_CUSTOMER_ID in number,
103 P_RESOURCE_ID in number,
104 p_qualifiers in IEM_OUTBOX_PROC_PUB.QualifierRecordList,
105 p_fwd_address in varchar2,
106 p_fwd_doc_id in number,
107 p_req_type in varchar2,
108 x_out_text out nocopy varchar2,
109 x_status out nocopy varchar2);
110
111 procedure IEM_PROCESS_INTENT(l_email_account_id in number,
112 l_msg_id in number,
113 l_theme_status out nocopy varchar2,
114 l_out_text out nocopy varchar2);
115
116 PROCEDURE ReprocessAutoreply(p_api_version_number IN NUMBER,
117 p_init_msg_list IN VARCHAR2 ,
118 p_commit IN VARCHAR2 ,
119 p_media_id in number,
120 p_interaction_id in number,
121 p_customer_id in number,
122 p_contact_id in number,
123 p_relationship_id in number,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2);
127 procedure IEM_WF_SPECIFICSEARCH(
128 l_msg_id in number,
129 l_email_account_id in number,
130 l_classification_id in number,
131 l_category_id AMV_SEARCH_PVT.amv_number_varray_type,
132 l_repos in varchar2,
133 l_stat out nocopy varchar2,
134 l_out_text out nocopy varchar2);
135 procedure IEM_RETURN_ENCRYPTID
136 (p_subject in varchar2,
137 x_id out nocopy varchar2,
138 x_Status out nocopy varchar2);
139
140
141 --added by siahmed for 12.1.3 advanced sr project
142 PROCEDURE advanced_sr_processing (
143 p_message_id IN NUMBER,
144 p_parser_id IN NUMBER,
145 p_account_type IN VARCHAR2 DEFAULT NULL,
146 p_default_type_id IN NUMBER DEFAULT NULL,
147 p_default_customer_id IN NUMBER DEFAULT NULL,
148 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
149 p_commit IN VARCHAR2 := FND_API.G_FALSE,
150 p_note IN VARCHAR2,
151 p_subject IN VARCHAR2,
152 p_note_type IN VARCHAR2,
153 p_contact_id IN NUMBER := NULL,
154 p_contact_point_id IN NUMBER := NULL,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2,
158 x_request_id OUT NOCOPY NUMBER
159 );
160
161 Procedure getCustomerNumber (
162 p_customer_number IN VARCHAR2 DEFAULT NULL,
163 p_customer_name IN VARCHAR2 DEFAULT NULL,
164 p_account_number IN VARCHAR2 DEFAULT NULL,
165 p_customer_phone IN VARCHAR2 DEFAULT NULL,
166 p_customer_email IN VARCHAR2 DEFAULT NULL,
167 p_instance_number IN VARCHAR2 DEFAULT NULL,
168 p_instance_serial_number IN VARCHAR2 DEFAULT NULL,
169 p_incident_site_number IN VARCHAR2 DEFAULT NULL,
170 --contact related stuff to find customerNumber
171 p_contact_number IN VARCHAR2 DEFAULT NULL,
172 p_contact_name IN VARCHAR2 DEFAULT NULL,
173 p_contact_phone IN VARCHAR2 DEFAULT NULL,
174 p_contact_email IN VARCHAR2 DEFAULT NULL,
175 x_customer_id OUT NOCOPY NUMBER);
176
177 Procedure getAccountNumber(p_account_number IN VARCHAR2,
178 x_cust_account_id OUT NOCOPY NUMBER);
179
180 Procedure getCustomerPhone(p_customer_phone IN VARCHAR2,
181 x_customer_phone_id OUT NOCOPY NUMBER);
182
183 Procedure getCustomerEmail (p_customer_email VARCHAR2,
184 x_customer_email_id out NOCOPY number);
185
186 Procedure getInstanceNumber (p_instance_number IN VARCHAR2,
187 p_cust_account_id IN NUMBER DEFAULT NULL,
188 x_customer_product_id OUT NOCOPY NUMBER,
189 x_inventory_org_id OUT NOCOPY NUMBER,
190 x_inventory_item_id OUT NOCOPY NUMBER);
191
192 Procedure getInstanceSerialNumber (p_instance_serial_number IN VARCHAR2,
193 p_cust_account_id IN NUMBER DEFAULT NULL,
194 x_customer_product_id OUT NOCOPY NUMBER,
195 x_inventory_org_id OUT NOCOPY NUMBER,
196 x_inventory_item_id OUT NOCOPY NUMBER);
197
198 Procedure getIncidentSiteNumber (p_incident_site_number IN varchar2,
199 x_incident_location_id out NOCOPY NUMBER);
200
201
202 Procedure getContactNumber (p_contact_number IN VARCHAR2,
203 p_parser_id IN NUMBER,
204 p_contact_phone IN VARCHAR2,
205 p_contact_email IN VARCHAR2,
206 x_contact_party_id OUT NOCOPY NUMBER,
207 x_contact_type OUT NOCOPY VARCHAR2,
208 x_contact_point_type OUT NOCOPY VARCHAR2,
209 x_contact_point_id OUT NOCOPY NUMBER);
210
211
212 Procedure getContactName (p_contact_name IN VARCHAR2,
213 p_parser_id IN NUMBER,
214 p_contact_phone IN VARCHAR2,
215 p_contact_email IN VARCHAR2,
216 x_contact_party_id OUT NOCOPY NUMBER,
217 x_contact_type OUT NOCOPY VARCHAR2,
218 x_contact_point_type OUT NOCOPY VARCHAR2,
219 x_contact_point_id OUT NOCOPY NUMBER);
220
221 Procedure getContactPhone (p_contact_phone IN VARCHAR2,
222 x_contact_party_id OUT NOCOPY NUMBER,
223 x_contact_type OUT NOCOPY VARCHAR2,
224 x_contact_point_type OUT NOCOPY VARCHAR2,
225 x_contact_point_id OUT NOCOPY NUMBER);
226
227 Procedure getContactEmail (p_contact_email IN VARCHAR2,
228 x_contact_party_id OUT NOCOPY NUMBER,
229 x_contact_type OUT NOCOPY VARCHAR2,
230 x_contact_point_type OUT NOCOPY VARCHAR2,
231 x_contact_point_id OUT NOCOPY NUMBER);
232
233 Procedure getInventoryItemName (p_inventory_item_name IN VARCHAR2,
234 x_inventory_item_id OUT NOCOPY NUMBER,
235 x_inventory_org_id OUT NOCOPY NUMBER);
236
237 PROCEDURE getServiceRequestType (p_service_request_type IN VARCHAR2,
238 p_default_type_id IN NUMBER,
239 x_type_id OUT NOCOPY NUMBER);
240
241 Procedure getProblemCode (p_problem_code in VARCHAR2,
242 x_problem_code OUT NOCOPY varchar2);
243
244 Procedure getUrgency (p_urgency IN VARCHAR2,
245 x_urgency_id OUT NOCOPY NUMBER);
246
247 Procedure getSiteName(p_site_name IN VARCHAR2,
248 x_party_site_id OUT NOCOPY NUMBER);
249
250 Procedure getExtReference(p_ext_ref IN VARCHAR2,
251 p_customer_product_id IN NUMBER,
252 x_ext_ref OUT NOCOPY VARCHAR2);
253
254 --siahmed
255 -- this is for bug 12663292
256 -- where we are trying to get the bill_to and ship_to information
257 Procedure getBillToShipTo(p_customer_id IN NUMBER,
258 p_contact_party_id IN NUMBER,
259 x_bill_to_site_id OUT NOCOPY NUMBER,
260 x_ship_to_site_id OUT NOCOPY NUMBER,
261 x_account_id OUT NOCOPY NUMBER
262 );
263 FUNCTION GET_TAG_DATA
264 ( p_start_tag IN VARCHAR2,
265 p_END_tag IN VARCHAR2,
266 p_message_id IN NUMBER
267 ) return VARCHAR2;
268
269 --end of siahmed 12.1.3 stuff
270
271 end IEM_EMAIL_PROC_PVT;
[Home] [Help]