[Home] [Help]
PACKAGE BODY: APPS.IBE_OM_INTEGRATION_GRP
Source
1 PACKAGE BODY IBE_OM_INTEGRATION_GRP AS
2 /*$Header: IBEGORDB.pls 120.1 2005/08/17 03:05:26 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_OM_INTEGRATION_GRP';
5 g_ItemType Varchar2(10) := 'IBEALERT';
6 g_processName Varchar2(30) := 'PROCESSMAP';
7
8
9 -- =======================================================================
10
11 -- PROCEDURE notify_rma_request_action
12
13 PROCEDURE notify_rma_request_action(
14 P_Api_Version_Number IN NUMBER,
15 P_Init_Msg_List IN VARCHAR2,
16 P_order_header_id IN NUMBER,
17 P_notif_context IN VARCHAR2,
18 P_comments IN VARCHAR2,
19 P_reject_reason_code IN VARCHAR2,
20 X_Return_Status OUT NOCOPY VARCHAR2,
21 X_Msg_Count OUT NOCOPY NUMBER,
22 X_Msg_Data OUT NOCOPY VARCHAR2)
23 IS
24
25
26 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
27 l_event_type VARCHAR2(20):= 'RETURNORDERAPPROVED';
28 l_party_id NUMBER;
29 l_user_name WF_USERS.NAME%TYPE;
30 l_order_header_id NUMBER;
31 l_notifEnabled VARCHAR2(3) := 'Y';
32 l_notifName VARCHAR2(30) ;
33 l_Orgid NUMBER := null;
34 l_messageName WF_MESSAGES.NAME%TYPE;
35 l_msgEnabled VARCHAR2(3);
36 l_item_owner WF_USERS.NAME%TYPE := 'SYSADMIN';
37 l_url VARCHAR2(30) :=null;
38 l_order_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE;
39 l_org_id OE_ORDER_HEADERS_ALL.ORG_ID%TYPE;
40 l_usertype VARCHAR2(30);
41 l_reject_reason_desc WF_LOOKUPS.MEANING%TYPE;
42 l_reject_reason_code WF_LOOKUPS.LOOKUP_CODE%TYPE;
43
44
45 CURSOR c_get_ord_num(p_order_header_id NUMBER) IS
46 SELECT order_number, org_id
47 FROM oe_order_headers_all
48 WHERE header_id = p_order_header_id;
49
50 CURSOR c_get_party_id(p_order_header_id NUMBER) IS
51 SELECT fnd.customer_id
52 FROM fnd_user fnd,
53 oe_order_headers_all oe
54 WHERE oe.header_id = p_order_header_id
55 AND oe.created_by = fnd.user_id;
56
57 CURSOR c_get_reject_reason_desc(p_reject_reason_code VARCHAR2) IS
58 SELECT meaning
59 FROM wf_lookups
60 WHERE lookup_type = 'OE_RMA_REJECTION_REASON'
61 AND lookup_code = p_reject_reason_code;
62
63
64
65 BEGIN
66
67
68
69
70 IF P_notif_context = G_RETURN_APPROVAL THEN
71
72 -- Assign notif name
73
74 l_notifName := 'IBE_RETURNORDERAPPROVED';
75
76 -- Check whether this notification is enabled
77
78 l_notifEnabled := IBE_WF_NOTIF_SETUP_PVT.Check_Notif_Enabled(l_notifName);
79
80 IF l_notifEnabled = 'Y' THEN
81
82
83
84 -- get the order number and org_id for the given order_header_id
85
86 FOR c_order_num_rec IN c_get_ord_num(p_order_header_id)
87 LOOP
88 l_order_number := c_order_num_rec.order_number;
89 l_org_id := c_order_num_rec.org_id;
90 END LOOP;
91
92 -- get party_id
93
94 FOR c_party_rec IN c_get_party_id(p_order_header_id)
95 LOOP
96 l_party_id := c_party_rec.customer_id;
97 l_user_name:= 'HZ_PARTY:'||l_party_id;
98 END LOOP;
99
100 -- get the user type
101
102 l_usertype:= NULL;
103
104 ibe_workflow_pvt.getUserType(l_party_id,l_usertype);
105
106
107 IBE_WF_MSG_MAPPING_PVT.Retrieve_Msg_Mapping(
108 p_org_id => l_OrgId ,
109 p_msite_id => NULL ,
110 p_user_type => NULL,
111 p_notif_name => l_notifName ,
112 x_enabled_flag => l_msgEnabled ,
113 x_wf_message_name => l_MessageName ,
114 x_return_status => x_return_status,
115 x_msg_data => x_msg_data ,
116 x_msg_count => X_Msg_Count);
117
118
119
120 IF l_msgEnabled = 'Y' Then
121
122 l_item_key := l_event_type||'-'||to_char(sysdate,'MMDDYYHH24MISS')||'-'||p_order_header_id;
123
124 wf_engine.CreateProcess(
125 itemtype => g_ItemType,
126 itemkey => l_item_key,
127 process => g_processName);
128
129 wf_engine.SetItemUserKey(
130 itemtype => g_ItemType,
131 itemkey => l_item_key,
132 userkey => l_item_key);
133
134 wf_engine.SetItemAttrText(
135 itemtype => g_ItemType,
136 itemkey => l_item_key,
137 aname => 'MESSAGE',
138 avalue => l_MessageName);
139
140 wf_engine.SetItemAttrText(
141 itemtype => g_ItemType,
142 itemkey => l_item_key,
143 aname => 'ITEMKEY',
144 avalue => l_item_key);
145
146 wf_engine.SetItemAttrText(
147 itemtype => g_ItemType,
148 itemkey => l_item_key,
149 aname => 'EVENTTYPE',
150 avalue => l_event_type);
151
152
153 wf_engine.SetItemAttrText(
154 itemtype => g_ItemType,
155 itemkey => l_item_key,
156 aname => 'ORDERNUMBER',
157 avalue => l_order_number);
158
159 wf_engine.SetItemAttrText(
160 itemtype => g_ItemType,
161 itemkey => l_item_key,
162 aname => 'URL',
163 avalue => l_url);
164
165 wf_engine.SetItemAttrText(
166 itemtype => g_ItemType,
167 itemkey => l_item_key,
168 aname => 'SENDTO',
169 avalue => l_user_name);
170
171 wf_engine.SetItemOwner(
172 itemtype => g_ItemType,
173 itemkey => l_item_key,
174 owner => l_item_owner);
175
176 wf_engine.StartProcess(
177 itemtype => g_ItemType,
178 itemkey => l_item_key);
179
180
181 END IF; --Msg Enabled
182
183
184 END IF; --Notification Enabled
185
186 ELSE
187
188 -- set event_type
189
190 l_event_type := 'RETURNORDERREJECTED';
191
192 -- Assign notif name
193
194 l_notifName := 'IBE_RETURNORDERREJECTED';
195
196 -- Check whether this notification is enabled
197
198 l_notifEnabled := IBE_WF_NOTIF_SETUP_PVT.Check_Notif_Enabled(l_notifName);
199
200 IF l_notifEnabled = 'Y' THEN
201
202
203
204 -- get the order number and org_id for the given order_header_id
205
206 FOR c_order_num_rec IN c_get_ord_num(p_order_header_id)
207 LOOP
208 l_order_number := c_order_num_rec.order_number;
209 l_org_id := c_order_num_rec.org_id;
210 END LOOP;
211
212 -- get party_id
213
214 FOR c_party_rec IN c_get_party_id(p_order_header_id)
215 LOOP
216 l_party_id := c_party_rec.customer_id;
217 l_user_name:= 'HZ_PARTY:'||l_party_id;
218 END LOOP;
219
220 -- get the user type
221
222 l_usertype:= NULL;
223
224 ibe_workflow_pvt.getUserType(l_party_id,l_usertype);
225
226 -- get reject reason code meaning from wf_lookups
227
228 IF p_reject_reason_code is NULL THEN
229 l_reject_reason_code := 'NONE';
230 ELSE
231 l_reject_reason_code := p_reject_reason_code;
232 END IF;
233
234 FOR c_reject_reason_desc_rec IN c_get_reject_reason_desc(l_reject_reason_code)
235 LOOP
236 l_reject_reason_desc := c_reject_reason_desc_rec.meaning;
237 END LOOP;
238
239
240
241 IBE_WF_MSG_MAPPING_PVT.Retrieve_Msg_Mapping(
242 p_org_id => l_OrgId ,
243 p_msite_id => NULL ,
244 p_user_type => NULL,
245 p_notif_name => l_notifName ,
246 x_enabled_flag => l_msgEnabled ,
247 x_wf_message_name => l_MessageName ,
248 x_return_status => x_return_status,
249 x_msg_data => x_msg_data ,
250 x_msg_count => X_Msg_Count);
251
252
253 IF l_msgEnabled = 'Y' Then
254
255 l_item_key:= NULL;
256
257
258
259 l_item_key := l_event_type||'REJECT'||'-'||to_char(sysdate,'MMDDYYHH24MISS')||'-'||p_order_header_id;
260
261
262
263
264 wf_engine.CreateProcess(
265 itemtype => g_ItemType,
266 itemkey => l_item_key,
267 process => g_processName);
268
269 wf_engine.SetItemUserKey(
270 itemtype => g_ItemType,
271 itemkey => l_item_key,
272 userkey => l_item_key);
273
274 wf_engine.SetItemAttrText(
275 itemtype => g_ItemType,
276 itemkey => l_item_key,
277 aname => 'MESSAGE',
278 avalue => l_MessageName);
279
280
281 wf_engine.SetItemAttrText(
282 itemtype => g_ItemType,
283 itemkey => l_item_key,
284 aname => 'ITEMKEY',
285 avalue => l_item_key);
286
287
288 wf_engine.SetItemAttrText(
289 itemtype => g_ItemType,
290 itemkey => l_item_key,
291 aname => 'EVENTTYPE',
292 avalue => l_event_type);
293
294
295 wf_engine.SetItemAttrText(
296 itemtype => g_ItemType,
297 itemkey => l_item_key,
298 aname => 'ORDERNUMBER',
299 avalue => l_order_number);
300
301 wf_engine.SetItemAttrText(
302 itemtype => g_ItemType,
303 itemkey => l_item_key,
304 aname => 'URL',
305 avalue => l_url);
306
307 wf_engine.SetItemAttrText(
308 itemtype => g_ItemType,
309 itemkey => l_item_key,
310 aname => 'REASON',
311 avalue => l_reject_reason_desc);
312
313
314 wf_engine.SetItemAttrText(
315 itemtype => g_ItemType,
316 itemkey => l_item_key,
317 aname => 'COMMENTS',
318 avalue => P_comments);
319
320
321 wf_engine.SetItemAttrText(
322 itemtype => g_ItemType,
323 itemkey => l_item_key,
324 aname => 'SENDTO',
325 avalue => l_user_name);
326
327 wf_engine.SetItemOwner(
328 itemtype => g_ItemType,
329 itemkey => l_item_key,
330 owner => l_item_owner);
331
332 wf_engine.StartProcess(
333 itemtype => g_ItemType,
334 itemkey => l_item_key);
335
336
337
338 END IF; --Msg Enabled
339
340 END IF; --Notification Enabled
341
342
343 END IF; -- notif_context check
344
345
346 EXCEPTION
347
348 WHEN OTHERS THEN
349 x_return_status := FND_API.g_ret_sts_error;
350
351
352 x_msg_count := 0;
353 wf_core.context('IBE_WORKFLOW_PVT',l_notifname,l_messagename,p_order_header_id);
354 RAISE;
355
356
357 END notify_rma_request_action;
358
359
360 END IBE_OM_INTEGRATION_GRP;