[Home] [Help]
PACKAGE BODY: APPS.IEM_ARCH_MSGDTLS_PVT
Source
1 PACKAGE BODY IEM_ARCH_MSGDTLS_PVT AS
2 /* $Header: iemvarmb.pls 120.3 2011/06/10 20:36:11 siahmed ship $ */
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 --Changes for Bug no 7293941
67 l_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
68 L_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
69
70 BEGIN
71 -- Standard Start of API savepoint
72 SAVEPOINT create_arch_msgdtls_PVT;
73
74 -- Standard call to check for call compatibility.
75
76 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
77 p_api_version_number,
78 l_api_name,
79 G_PKG_NAME)
80 THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 IF FND_API.to_Boolean( p_init_msg_list )
87 THEN
88 FND_MSG_PUB.initialize;
89 END IF;
90
91 -- Initialize API return status to SUCCESS
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 --begins here
95
96 --get next sequential number for msg_meta_id
97
98 if (p_message_id = FND_API.G_MISS_NUM) then -- MAIL_TYPE = outbound
99 SELECT IEM_MS_BASE_HEADERS_S1.nextval
100 INTO l_seq_id
101 FROM dual;
102 else
103 l_seq_id := p_message_id;
104 end if;
105
106 INSERT INTO IEM_ARCH_MSGDTLS
107 (
108 MESSAGE_ID,
109 INBOUND_MESSAGE_ID,
110 EMAIL_ACCOUNT_ID,
111 MAILPROC_STATUS,
112 RT_CLASSIFICATION_ID,
113 MAIL_TYPE,
114 FROM_STR,
115 REPLY_TO_STR,
116 TO_STR,
117 CC_STR,
118 BCC_STR,
119 SENT_DATE,
120 RECEIVED_DATE,
121 SUBJECT,
122 resource_id,
123 GROUP_ID,
124 IH_MEDIA_ITEM_ID,
125 CUSTOMER_ID,
126 MESSAGE_SIZE,
127 CONTACT_ID,
128 RELATIONSHIP_ID,
129 TOP_INTENT,
130 MESSAGE_TEXT,
131 ATTRIBUTE1,
132 ATTRIBUTE2,
133 ATTRIBUTE3,
134 ATTRIBUTE4,
135 ATTRIBUTE5,
136 ATTRIBUTE6,
137 ATTRIBUTE7,
138 ATTRIBUTE8,
139 ATTRIBUTE9,
140 ATTRIBUTE10,
141 ATTRIBUTE11,
142 ATTRIBUTE12,
143 ATTRIBUTE13,
144 ATTRIBUTE14,
145 ATTRIBUTE15,
146 CREATED_BY,
147 CREATION_DATE,
148 LAST_UPDATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATE_LOGIN
151 )
152 VALUES
153 (
154 l_seq_id,
155 decode(p_inbound_message_id, FND_API.G_MISS_NUM, null, p_inbound_message_id),
156 p_email_account_id,
157 p_mailproc_status,
158 decode(p_rt_classification_id, FND_API.G_MISS_NUM, null, p_rt_classification_id),
159 p_mail_type,
160 p_from_str,
161 decode(p_reply_to_str, FND_API.G_MISS_CHAR, null, p_reply_to_str),
162 p_to_str,
163 decode(p_cc_str, FND_API.G_MISS_CHAR, null, p_cc_str),
164 decode(p_bcc_str, FND_API.G_MISS_CHAR, null, p_bcc_str),
165 p_sent_date,
166 decode(p_received_date, FND_API.G_MISS_DATE, null, p_received_date),
167 decode(p_subject, FND_API.G_MISS_CHAR, null, p_subject),
168 decode(p_agent_id, FND_API.G_MISS_NUM, null, p_agent_id),
169 decode(p_group_id, FND_API.G_MISS_NUM, null, p_group_id),
170 decode(p_ih_media_item_id, FND_API.G_MISS_NUM, null, p_ih_media_item_id),
171 decode(p_customer_id, FND_API.G_MISS_NUM, null, p_customer_id),
172 p_message_size,
173 decode(p_contact_id, FND_API.G_MISS_NUM, null, p_contact_id),
174 decode(p_relationship_id, FND_API.G_MISS_NUM, null, p_relationship_id),
175 decode(p_top_intent, FND_API.G_MISS_CHAR, null, p_top_intent),
176 decode(p_message_text, FND_API.G_MISS_CHAR, null, p_message_text),
177 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
178 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
179 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
180 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
181 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
182 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
183 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
184 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
185 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
186 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
187 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
188 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
189 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
190 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
191 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
192 --Changes for Bug no 7293941
193 decode(l_created_updated_by,null,-1,l_created_updated_by),
194 sysdate,
195 decode(l_created_updated_by,null,-1,l_created_updated_by),
196 sysdate,
197 decode(L_LAST_UPDATE_LOGIN,null,-1,L_LAST_UPDATE_LOGIN)
198 );
199
200
201 x_message_id := l_seq_id;
202
203 -- Standard Check Of p_commit.
204 IF FND_API.To_Boolean(p_commit) THEN
205 COMMIT WORK;
206 END IF;
207
208
209 -- Standard callto get message count and if count is 1, get message info.
210 FND_MSG_PUB.Count_And_Get
211 ( p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214
215 EXCEPTION
216
217 WHEN FND_API.G_EXC_ERROR THEN
218 ROLLBACK TO create_arch_msgdtls_PVT;
219 x_return_status := FND_API.G_RET_STS_ERROR ;
220 FND_MSG_PUB.Count_And_Get
221
222 ( p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 ROLLBACK TO create_arch_msgdtls_PVT;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
229 FND_MSG_PUB.Count_And_Get
230 ( p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233
234 WHEN OTHERS THEN
235 ROLLBACK TO create_arch_msgdtls_PVT;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 IF FND_MSG_PUB.Check_Msg_Level
238 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
239 THEN
240 FND_MSG_PUB.Add_Exc_Msg
241 ( G_PKG_NAME ,
242 l_api_name
243 );
244 END IF;
245
246 FND_MSG_PUB.Count_And_Get
247 ( p_count => x_msg_count,
248 p_data => x_msg_data
249
250 );
251
252 END create_item;
253
254
255
256
257 END IEM_ARCH_MSGDTLS_PVT;