eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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 
76     WHEN default_rule_error THEN
77 
78        WF_CORE.CONTEXT ('inv_lot_serial_date_pkg',
79           'date_rule',
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;
381    WHEN invalid_callback THEN
378    END IF;
379 
380 EXCEPTION
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;