[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_SERIAL_DATE_PKG
Source
1 PACKAGE BODY INV_LOT_SERIAL_DATE_PKG AS
2 /* $Header: INVLSDTB.pls 120.1 2005/11/15 01:09:29 nsinghi noship $ */
3
4 FUNCTION date_rule (p_subscription_guid IN RAW,
5 p_event IN OUT NOCOPY wf_event_t) RETURN VARCHAR2
6 IS
7
8 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'INVDATNT';
9 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := p_event.getEventKey;
10
11 l_errname VARCHAR2(30);
12 l_errmsg VARCHAR2(2000);
13 l_errstack VARCHAR2(32000);
14
15 l_WorkflowProcess VARCHAR2(30) := 'INVDATNT_PROCESS';
16
17 default_rule_error EXCEPTION ;
18 l_status VARCHAR2 (8);
19 l_result VARCHAR2 (30);
20 l_return_status VARCHAR2(240);
21
22 BEGIN
23
24 /*Check if the workflow data exists and remove the same for the itemtype and itemkey
25 combination */
26 BEGIN
27 IF (WF_ITEM.ITEM_EXIST (l_itemtype, l_itemkey)) THEN
28
29 /* Check item status */
30 WF_ENGINE.ITEMSTATUS ( itemtype => l_itemtype,
31 itemkey => l_itemkey,
32 status => l_status,
33 result => l_result);
34
35 -- wf_item_activity_status.root_status (l_itemtype, l_itemkey, l_status, l_result);
36 /* If it is not completed then abort the process */
37 IF (l_status <> 'COMPLETE')THEN
38
39 WF_ENGINE.ABORTPROCESS (itemtype=> l_itemtype,
40 itemkey=> l_itemkey,
41 process=> l_workflowprocess);
42
43 END IF;
44 /* Purge the workflow data for workflow key */
45 WF_PURGE.TOTAL (itemtype=> l_itemtype,
46 itemkey=> l_itemkey,
47 docommit=> TRUE);
48
49 END IF;
50 EXCEPTION
51 WHEN OTHERS THEN
52
53 WF_CORE.CONTEXT ('inv_lot_serial_date_pkg', 'date_rule', l_itemtype, l_itemkey) ;
54 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
55 END;
56 BEGIN
57
58 /* Start the workflow */
59
60 l_return_status := WF_RULE.DEFAULT_RULE(p_subscription_guid=>p_subscription_guid,p_event=>p_event);
61
62 IF l_return_status = 'ERROR' THEN
63 ROLLBACK;
64 l_errmsg := p_event.GETERRORMESSAGE;
65 RAISE DEFAULT_RULE_ERROR;
66 ELSE
67 COMMIT;
68 END IF;
69
70 END ;
71
72 RETURN l_return_status;
73
74 EXCEPTION
75
79 'date_rule',
76 WHEN default_rule_error THEN
77
78 WF_CORE.CONTEXT ('inv_lot_serial_date_pkg',
80 'default_rule_error',
81 l_itemtype,
82 l_itemkey) ;
83 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
84
85 RETURN l_return_status;
86
87 WHEN OTHERS THEN
88
89 WF_CORE.CONTEXT ('inv_lot_serial_date_pkg',
90 'date_rule',
91 l_itemtype,
92 l_itemkey) ;
93 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
94 RETURN l_return_status;
95
96 END date_rule;
97
98 PROCEDURE send_notification (
99 p_itemtype IN VARCHAR2,
100 p_itemkey IN VARCHAR2,
101 p_actid IN NUMBER,
102 p_funcmode IN VARCHAR2,
103 p_resultout OUT NOCOPY VARCHAR2)
104
105 IS
106
107 l_approverList ame_util.approversTable;
108 l_approverList2 ame_util.approversTable2;
109 l_ruleids ame_util.idList;
110 l_rulenames ame_util.stringList;
111 approvalProcessCompleteYN ame_util.charType;
112 itemClasses ame_util.stringList;
113 itemIndexes ame_util.idList;
114 itemIds ame_util.stringList;
115 itemSources ame_util.longStringList;
116 ruleIndexes ame_util.idList;
117 sourceTypes ame_util.stringList;
118
119 l_process_complete VARCHAR2(100);
120 l_count NUMBER := 0;
121 l_notif_id NUMBER;
122 l_actionTypeId NUMBER;
123 l_lot_notifcation BOOLEAN := FALSE;
124 l_serial_notifcation BOOLEAN := FALSE;
125 l_errname VARCHAR2(30);
126 l_errmsg VARCHAR2(2000);
127 l_errstack VARCHAR2(32000);
128 l_application_id NUMBER;
129
130 l_organization_code VARCHAR2(3);
131 l_organization_id NUMBER;
132 l_item_number VARCHAR2(240);
133 l_item_id NUMBER;
134 l_item_category VARCHAR2(240);
135 l_category_id NUMBER;
136 l_query_for VARCHAR2(100);
137 l_lot_number VARCHAR2(100);
138 l_serial_number VARCHAR2(100);
139 l_date_context VARCHAR2(240);
140 l_date_type VARCHAR2(240);
141 l_user_date_type VARCHAR2(240);
142 l_date_value VARCHAR2(240);
143 l_action_code VARCHAR2(240);
144 l_serial_status VARCHAR2(240);
145 l_onhand_qty_uom VARCHAR2(240);
146 l_context VARCHAR2(32000);
147 l_transaction_id NUMBER;
148
149
150 BEGIN
151
152 l_organization_id := WF_ENGINE.GETITEMATTRNUMBER
153 (itemtype => p_itemtype,
154 itemkey => p_itemkey,
155 aname => 'ORGANIZATION_ID');
156
157 l_organization_code := WF_ENGINE.GETITEMATTRTEXT
158 (itemtype => p_itemtype,
159 itemkey => p_itemkey,
160 aname => 'ORGANIZATION_CODE');
161
162 l_item_id := WF_ENGINE.GETITEMATTRNUMBER
163 (itemtype => p_itemtype,
164 itemkey => p_itemkey,
165 aname => 'ITEM_ID');
166
167 l_item_number := WF_ENGINE.GETITEMATTRTEXT
168 (itemtype => p_itemtype,
169 itemkey => p_itemkey,
170 aname => 'ITEM_NUMBER');
171
172 l_category_id := WF_ENGINE.GETITEMATTRNUMBER
173 (itemtype => p_itemtype,
174 itemkey => p_itemkey,
175 aname => 'CATEGORY_ID');
176
177 l_item_category := WF_ENGINE.GETITEMATTRTEXT
178 (itemtype => p_itemtype,
179 itemkey => p_itemkey,
180 aname => 'ITEM_CATEGORY');
181
182 l_query_for := WF_ENGINE.GETITEMATTRTEXT
183 (itemtype => p_itemtype,
184 itemkey => p_itemkey,
185 aname => 'QUERY_FOR');
186
187 l_lot_number := WF_ENGINE.GETITEMATTRTEXT
188 (itemtype => p_itemtype,
189 itemkey => p_itemkey,
190 aname => 'LOT_NUMBER');
191
192 l_serial_number := WF_ENGINE.GETITEMATTRTEXT
193 (itemtype => p_itemtype,
194 itemkey => p_itemkey,
195 aname => 'SERIAL_NUMBER');
196
197 l_date_context := WF_ENGINE.GETITEMATTRTEXT
198 (itemtype => p_itemtype,
199 itemkey => p_itemkey,
200 aname => 'DATE_CONTEXT');
201
202 l_date_type := WF_ENGINE.GETITEMATTRTEXT
203 (itemtype => p_itemtype,
204 itemkey => p_itemkey,
205 aname => 'DATE_TYPE');
206
207 l_user_date_type := WF_ENGINE.GETITEMATTRTEXT
208 (itemtype => p_itemtype,
209 itemkey => p_itemkey,
210 aname => 'USER_DATE_TYPE');
211
212 l_date_value := WF_ENGINE.GETITEMATTRTEXT
213 (itemtype => p_itemtype,
214 itemkey => p_itemkey,
215 aname => 'DATE_VALUE');
216
217 l_action_code := WF_ENGINE.GETITEMATTRTEXT
218 (itemtype => p_itemtype,
219 itemkey => p_itemkey,
220 aname => 'ACTION_CODE');
221
222 l_serial_status := WF_ENGINE.GETITEMATTRTEXT
223 (itemtype => p_itemtype,
224 itemkey => p_itemkey,
225 aname => 'SERIAL_STATUS');
226
227 l_onhand_qty_uom := WF_ENGINE.GETITEMATTRTEXT
228 (itemtype => p_itemtype,
229 itemkey => p_itemkey,
230 aname => 'ONHAND_QTY_UOM');
231
232 l_transaction_id := WF_ENGINE.GETITEMATTRNUMBER
233 (itemtype => p_itemtype,
234 itemkey => p_itemkey,
235 aname => 'TRANSACTION_ID');
236
237 IF l_lot_number IS NULL THEN
238 l_serial_notifcation := TRUE;
239 ELSIF l_serial_number IS NULL THEN
240 l_lot_notifcation := TRUE;
241 END IF;
242
243 SELECT application_id INTO l_application_id
244 FROM fnd_application WHERE application_short_name='INV';
245
246 l_context := l_organization_id||'+-?*'||l_item_id||'+-?*'||l_category_id||'+-?*'||l_query_for||'+-?*'
247 ||l_lot_number||'+-?*'||l_serial_number||'+-?*'||l_date_context||'+-?*'||l_date_type
248 ||'+-?*'||l_user_date_type||'+-?*'||l_date_value||'+-?*'||l_action_code||'+-?*'
249 ||l_serial_status||'+-?*'||l_onhand_qty_uom;
250
251 IF l_serial_notifcation OR l_lot_notifcation THEN
252 ame_api2.getAllApprovers6 (applicationIdIn => l_application_id,
253 transactionTypeIn => 'oracle.apps.inv.Date.Notification',
254 -- transactionIdIn => p_itemkey,
255 transactionIdIn => l_transaction_id,
256 approvalProcessCompleteYNOut => approvalProcessCompleteYN,
257 approversOut => l_approverList2,
258 itemIndexesOut => itemIndexes,
259 itemClassesOut => itemClasses,
260 itemIdsOut => itemIds,
261 itemSourcesOut => itemSources,
262 ruleIndexesOut => ruleIndexes,
263 sourceTypesOut => sourceTypes,
264 ruleIdsOut => l_ruleIds,
265 ruleDescriptionsOut => l_rulenames);
266
267 IF l_approverList2.COUNT > 0 THEN
268 FOR i IN l_approverList2.FIRST..l_approverList2.LAST LOOP
269
270 IF l_lot_notifcation THEN
271
272 l_notif_id := WF_NOTIFICATION.SEND
273 (ROLE => l_approverList2(i).name,
274 MSG_TYPE => 'INVDATNT',
275 MSG_NAME => 'INVDATNT_LOT_MSG',
276 CALLBACK => 'INV_LOT_SERIAL_DATE_PKG.GET_MESSAGE_ATTRS',
277 CONTEXT => l_context);
278 ELSE
279
280 l_notif_id := WF_NOTIFICATION.SEND
281 (ROLE => l_approverList2(i).name,
282 MSG_TYPE => 'INVDATNT',
283 MSG_NAME => 'INVDATNT_SERIAL_MSG',
284 CALLBACK => 'INV_LOT_SERIAL_DATE_PKG.GET_MESSAGE_ATTRS',
285 CONTEXT => l_context);
286 END IF;
287 END LOOP;
288 END IF;
289 END IF;
290
291 --Bug - 4733445. No longer need reference to the following table.
292 -- DELETE FROM inv_ame_transactions_temp WHERE transaction_id = l_transaction_id;
293
294 END send_notification;
295
296 PROCEDURE get_message_attrs
297 (
298 command IN VARCHAR2,
299 context IN VARCHAR2,
300 attr_name IN VARCHAR2,
301 attr_type IN VARCHAR2,
302 text_value IN OUT NOCOPY VARCHAR2,
303 number_value IN OUT NOCOPY NUMBER,
304 date_value IN OUT NOCOPY DATE
305 )
306 IS
307 invalid_callback EXCEPTION ;
308 l_errname VARCHAR2(30);
309 l_errmsg VARCHAR2(2000);
310 l_errstack VARCHAR2(32000);
311
312 BEGIN
313
314 IF command <> 'GET' THEN
315 RAISE invalid_callback;
316 ELSE
317 IF attr_name = 'ORGANIZATION_CODE' THEN
318 SELECT organization_code INTO text_value
319 FROM mtl_parameters
320 WHERE to_char(organization_id) = SUBSTR(context, 1, (INSTR(context, '+-?*', 1, 1)-1)) ;
321
322 ELSIF attr_name = 'ORGANIZATION_ID' THEN
323 number_value := to_number(SUBSTR(context, 1, (INSTR(context, '+-?*', 1, 1)-1)));
324
325 ELSIF attr_name = 'ITEM_NUMBER' THEN
326 SELECT concatenated_segments INTO text_value
327 FROM mtl_system_items_kfv
328 WHERE to_char(organization_id) = SUBSTR(context, 1, (INSTR(context, '+-?*', 1, 1)-1))
329 AND to_char(inventory_item_id) = SUBSTR(context, (INSTR(context, '+-?*', 1, 1)+4), INSTR(context, '+-?*', 1, 2) - (INSTR(context, '+-?*', 1, 1)+4));
330
331 ELSIF attr_name = 'ITEM_ID' THEN
332 number_value := TO_NUMBER(SUBSTR(context, (INSTR(context, '+-?*', 1, 1)+4), INSTR(context, '+-?*', 1, 2) - (INSTR(context, '+-?*', 1, 1)+4)));
333
334 ELSIF attr_name = 'ITEM_CATEGORY' THEN
335 SELECT category_concat_segs INTO text_value
336 FROM mtl_categories_v
337 WHERE to_char(category_id) = SUBSTR(context, (INSTR(context, '+-?*', 1, 2)+4), INSTR(context, '+-?*', 1, 3) - (INSTR(context, '+-?*', 1, 2)+4));
338
339 ELSIF attr_name = 'CATEGORY_ID' THEN
340 number_value := TO_NUMBER(SUBSTR(context, (INSTR(context, '+-?*', 1, 2)+4), INSTR(context, '+-?*', 1, 3) - (INSTR(context, '+-?*', 1, 2)+4)));
341
342 ELSIF attr_name = 'QUERY_FOR' THEN
343 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 3)+4), INSTR(context, '+-?*', 1, 4) - (INSTR(context, '+-?*', 1, 3)+4));
344
345 ELSIF attr_name = 'LOT_NUMBER' THEN
346 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 4)+4), INSTR(context, '+-?*', 1, 5) - (INSTR(context, '+-?*', 1, 4)+4));
347
348 ELSIF attr_name = 'SERIAL_NUMBER' THEN
349 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 5)+4), INSTR(context, '+-?*', 1, 6) - (INSTR(context, '+-?*', 1, 5)+4));
350
351 ELSIF attr_name = 'DATE_CONTEXT' THEN
352 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 6)+4), INSTR(context, '+-?*', 1, 7) - (INSTR(context, '+-?*', 1, 6)+4));
353
354 ELSIF attr_name = 'DATE_TYPE' THEN
355 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 7)+4), INSTR(context, '+-?*', 1, 8) - (INSTR(context, '+-?*', 1, 7)+4));
356
357 ELSIF attr_name = 'USER_DATE_TYPE' THEN
358 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 8)+4), INSTR(context, '+-?*', 1, 9) - (INSTR(context, '+-?*', 1, 8)+4));
359
360 ELSIF attr_name = 'DATE_VALUE' THEN
361 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 9)+4), INSTR(context, '+-?*', 1, 10) - (INSTR(context, '+-?*', 1, 9)+4));
362
363 ELSIF attr_name = 'ACTION_CODE' THEN
364 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 10)+4), INSTR(context, '+-?*', 1, 11) - (INSTR(context, '+-?*', 1, 10)+4));
365
366 ELSIF attr_name = 'SERIAL_STATUS' THEN
367 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 11)+4), INSTR(context, '+-?*', 1, 12) - (INSTR(context, '+-?*', 1, 11)+4));
368
369 ELSIF attr_name = 'ONHAND_QTY_UOM' THEN
370 text_value := SUBSTR(context, (INSTR(context, '+-?*', 1, 12)+4));
371
372 ELSIF attr_name = '.MAIL_QUERY' THEN
373 text_value := NULL;
374
375 ELSE
376 RAISE invalid_callback;
377 END IF;
378 END IF;
379
380 EXCEPTION
381 WHEN invalid_callback THEN
382 WF_CORE.CONTEXT ('inv_lot_serial_date_pkg',
383 'get_message_attrs',
384 'invalid_callback') ;
385 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
386
387 WHEN OTHERS THEN
388 WF_CORE.CONTEXT ('inv_lot_serial_date_pkg',
389 'get_message_attrs',
390 'exception : others') ;
391 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
392
393 END get_message_attrs;
394
395 END INV_LOT_SERIAL_DATE_PKG;