[Home] [Help]
PACKAGE BODY: APPS.IEM_ARCH_MSGDTLS_PVT
Source
1 PACKAGE BODY IEM_ARCH_MSGDTLS_PVT AS
2 /* $Header: iemvarmb.pls 120.0 2005/06/02 14:00:47 appldev noship $ */
3
4
5 -- Enter procedure, function bodies as shown below
6 G_PKG_NAME CONSTANT varchar2(30) :='IEM_MSG_ARCHDTLS_PVT ';
7 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
8 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
9
10 PROCEDURE create_item (
11 p_api_version_number IN NUMBER,
12 p_init_msg_list IN VARCHAR2 := null,
13 p_commit IN VARCHAR2 := null,
14 p_message_id IN NUMBER,
15 p_inbound_message_id IN NUMBER,
16 p_email_account_id IN NUMBER,
17 p_mailproc_status IN VARCHAR2,
18 p_rt_classification_id IN NUMBER,
19 p_mail_type NUMBER,
20 p_from_str IN VARCHAR2,
21 p_reply_to_str IN VARCHAR2,
22 p_to_str IN VARCHAR2,
23 p_cc_str IN VARCHAR2,
24 p_bcc_str IN VARCHAR2,
25 p_sent_date IN VARCHAR2,
26 p_received_date IN DATE,
27 p_subject IN VARCHAR2,
28 p_agent_id IN NUMBER,
29 p_group_id IN NUMBER,
30 p_ih_media_item_id IN NUMBER,
31 p_customer_id IN NUMBER,
32 p_message_size IN NUMBER,
33 p_contact_id IN NUMBER,
34 p_relationship_id IN NUMBER,
35 p_top_intent IN VARCHAR2,
36 p_message_text IN VARCHAR2,
37 p_ATTRIBUTE1 IN VARCHAR2,
38 p_ATTRIBUTE2 IN VARCHAR2,
39 p_ATTRIBUTE3 IN VARCHAR2,
40 p_ATTRIBUTE4 IN VARCHAR2,
41 p_ATTRIBUTE5 IN VARCHAR2,
42 p_ATTRIBUTE6 IN VARCHAR2,
43 p_ATTRIBUTE7 IN VARCHAR2,
44 p_ATTRIBUTE8 IN VARCHAR2,
45 p_ATTRIBUTE9 IN VARCHAR2,
46 p_ATTRIBUTE10 IN VARCHAR2,
47 p_ATTRIBUTE11 IN VARCHAR2,
48 p_ATTRIBUTE12 IN VARCHAR2,
49 p_ATTRIBUTE13 IN VARCHAR2,
50 p_ATTRIBUTE14 IN VARCHAR2,
51 p_ATTRIBUTE15 IN VARCHAR2,
52 x_message_id OUT NOCOPY NUMBER,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2
56 ) is
57 l_api_name VARCHAR2(255):='create_arch_msgdtls';
58 l_api_version_number NUMBER:=1.0;
59 l_seq_id NUMBER;
60
61 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
62 l_msg_count NUMBER := 0;
63 l_msg_data VARCHAR2(2000);
64 i INTEGER;
65
66
67 BEGIN
68 -- Standard Start of API savepoint
69 SAVEPOINT create_arch_msgdtls_PVT;
70
71 -- Standard call to check for call compatibility.
72
73 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
74 p_api_version_number,
75 l_api_name,
76 G_PKG_NAME)
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80
81
82 -- Initialize message list if p_init_msg_list is set to TRUE.
83 IF FND_API.to_Boolean( p_init_msg_list )
84 THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87
88 -- Initialize API return status to SUCCESS
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 --begins here
92
93 --get next sequential number for msg_meta_id
94
95 if (p_message_id = FND_API.G_MISS_NUM) then -- MAIL_TYPE = outbound
96 SELECT IEM_MS_BASE_HEADERS_S1.nextval
97 INTO l_seq_id
98 FROM dual;
99 else
100 l_seq_id := p_message_id;
101 end if;
102
103 INSERT INTO IEM_ARCH_MSGDTLS
104 (
105 MESSAGE_ID,
106 INBOUND_MESSAGE_ID,
107 EMAIL_ACCOUNT_ID,
108 MAILPROC_STATUS,
109 RT_CLASSIFICATION_ID,
110 MAIL_TYPE,
111 FROM_STR,
112 REPLY_TO_STR,
113 TO_STR,
114 CC_STR,
115 BCC_STR,
116 SENT_DATE,
117 RECEIVED_DATE,
118 SUBJECT,
119 resource_id,
120 GROUP_ID,
121 IH_MEDIA_ITEM_ID,
122 CUSTOMER_ID,
123 MESSAGE_SIZE,
124 CONTACT_ID,
125 RELATIONSHIP_ID,
126 TOP_INTENT,
127 MESSAGE_TEXT,
128 ATTRIBUTE1,
129 ATTRIBUTE2,
130 ATTRIBUTE3,
131 ATTRIBUTE4,
132 ATTRIBUTE5,
133 ATTRIBUTE6,
134 ATTRIBUTE7,
135 ATTRIBUTE8,
136 ATTRIBUTE9,
137 ATTRIBUTE10,
138 ATTRIBUTE11,
139 ATTRIBUTE12,
140 ATTRIBUTE13,
141 ATTRIBUTE14,
142 ATTRIBUTE15,
143 CREATED_BY,
144 CREATION_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_DATE,
147 LAST_UPDATE_LOGIN
148 )
149 VALUES
150 (
151 l_seq_id,
152 decode(p_inbound_message_id, FND_API.G_MISS_NUM, null, p_inbound_message_id),
153 p_email_account_id,
154 p_mailproc_status,
155 decode(p_rt_classification_id, FND_API.G_MISS_NUM, null, p_rt_classification_id),
156 p_mail_type,
157 p_from_str,
158 decode(p_reply_to_str, FND_API.G_MISS_CHAR, null, p_reply_to_str),
159 p_to_str,
160 decode(p_cc_str, FND_API.G_MISS_CHAR, null, p_cc_str),
161 decode(p_bcc_str, FND_API.G_MISS_CHAR, null, p_bcc_str),
162 p_sent_date,
163 decode(p_received_date, FND_API.G_MISS_DATE, null, p_received_date),
164 decode(p_subject, FND_API.G_MISS_CHAR, null, p_subject),
165 decode(p_agent_id, FND_API.G_MISS_NUM, null, p_agent_id),
166 decode(p_group_id, FND_API.G_MISS_NUM, null, p_group_id),
167 decode(p_ih_media_item_id, FND_API.G_MISS_NUM, null, p_ih_media_item_id),
168 decode(p_customer_id, FND_API.G_MISS_NUM, null, p_customer_id),
169 p_message_size,
170 decode(p_contact_id, FND_API.G_MISS_NUM, null, p_contact_id),
171 decode(p_relationship_id, FND_API.G_MISS_NUM, null, p_relationship_id),
172 decode(p_top_intent, FND_API.G_MISS_CHAR, null, p_top_intent),
173 decode(p_message_text, FND_API.G_MISS_CHAR, null, p_message_text),
174 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
175 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
176 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
177 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
178 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
179 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
180 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
181 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
182 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
183 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
184 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
185 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
186 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
187 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
188 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
189 decode(G_created_updated_by,null,-1,G_created_updated_by),
190 sysdate,
191 decode(G_created_updated_by,null,-1,G_created_updated_by),
192 sysdate,
193 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
194 );
195
196
197 x_message_id := l_seq_id;
198
199 -- Standard Check Of p_commit.
200 IF FND_API.To_Boolean(p_commit) THEN
201 COMMIT WORK;
202 END IF;
203
204
205 -- Standard callto get message count and if count is 1, get message info.
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210
211 EXCEPTION
212
213 WHEN FND_API.G_EXC_ERROR THEN
214 ROLLBACK TO create_arch_msgdtls_PVT;
215 x_return_status := FND_API.G_RET_STS_ERROR ;
216 FND_MSG_PUB.Count_And_Get
217
218 ( p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 ROLLBACK TO create_arch_msgdtls_PVT;
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
225 FND_MSG_PUB.Count_And_Get
226 ( p_count => x_msg_count,
227 p_data => x_msg_data
228 );
229
230 WHEN OTHERS THEN
231 ROLLBACK TO create_arch_msgdtls_PVT;
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 IF FND_MSG_PUB.Check_Msg_Level
234 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
235 THEN
236 FND_MSG_PUB.Add_Exc_Msg
237 ( G_PKG_NAME ,
238 l_api_name
239 );
240 END IF;
241
242 FND_MSG_PUB.Count_And_Get
243 ( p_count => x_msg_count,
244 p_data => x_msg_data
245
246 );
247
248 END create_item;
249
250
251
252
253 END IEM_ARCH_MSGDTLS_PVT;