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.GME_BATCH_WKFL_PKG

Source


1 PACKAGE BODY GME_BATCH_WKFL_PKG AS
2 /*  $Header: GMEBTWFB.pls 120.1 2006/02/24 08:22:42 lgao noship $  */
3 /* +=========================================================================+
4  |                Copyright (c) 2002 Oracle Corporation                    |
5  |                         All righTs reserved                             |
6  +=========================================================================+
7  | FILENAME                                                                |
8  |    GMEBTWFB.pls                                                         |
9  |                                                                         |
10  | DESCRIPTION                                                             |
11  |     This package contains Workflow procedures for GME-OM Integration    |
12  |     reservation.                                                        |
13  |                                                                         |
14  | --  Init_wf()                                                           |
15  | --  Check_event()                                                       |
16  | --  Insert_gml_batch_so_workflow()                                      |
17  |                                                                         |
18  | HISTORY                                                                 |
19  |              10-Oct-2003  nchekuri        Created                       |
20  |                                                                         |
21  +=========================================================================+ */
22 
23 G_PKG_NAME  CONSTANT  VARCHAR2(30):='GME_BATCH_WORKFLOW_PKG';
24 g_debug               VARCHAR2 (5)  := fnd_profile.VALUE ('AFLOG_LEVEL');
25 
26 PROCEDURE init_wf (
27         p_itemtype           IN VARCHAR2
28       , p_itemkey            IN   NUMBER
29       , p_approver           IN   NUMBER
30       , p_so_header_id       IN   NUMBER
31       , p_so_line_id         IN   NUMBER
32       , p_batch_id           IN   NUMBER
33       , p_batch_line_id      IN   NUMBER
34       , p_fpo_id             IN   NUMBER
35       , p_organization_id    IN   NUMBER
36       , p_lot_no             IN   VARCHAR2 DEFAULT NULL
37       , p_action_code        IN   VARCHAR2
38  )IS
39 
40   l_api_name                 CONSTANT  VARCHAR2 (30)    := 'init_wf';
41   l_itemtype                 VARCHAR2(240) :=  'GMEBTRES';
42   --l_itemkey                  VARCHAR2(240) := to_char(p_session_id)||'-'||to_char(sysdate,'dd-MON-yyyy HH24:mi:ss');
43   l_itemkey                  VARCHAR2(240) ;
44   l_run_wf_in_background     CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
45   l_WorkflowProcess          VARCHAR2(30)  := 'GMEBTRES_PROCESS';
46   l_count                    NUMBER;
47 
48   l_order_no             NUMBER;
49   l_so_line_no           NUMBER;
50   l_batch_line_no        NUMBER;
51   l_item_no              VARCHAR2(40);
52   l_order_type           VARCHAR2(30);
53   l_batch_no             VARCHAR2(32);
54   l_fpo_no               VARCHAR2(32);
55   l_plant_code           VARCHAR2(4);
56   l_plan_complt_date     DATE;
57   l_approver_name        VARCHAR2(20);
58   l_organization_code    VARCHAR2(20);
59 
60   /* Cusor Declarations */
61 
62   CURSOR  Get_user_name(p_user_id IN NUMBER) IS
63      SELECT user_name
64        FROM fnd_user
65       WHERE user_id = p_user_id;
66 
67   CURSOR Get_order_info(p_so_line_id IN NUMBER) IS
68      SELECT  ol.line_number, mtl.segment1,
69              ot.transaction_type_code,
70              oh.order_number
71        FROM  oe_order_headers_all oh
72             ,oe_order_lines_all ol
73             ,oe_transaction_types_all ot
74             ,mtl_system_items mtl
75       WHERE  ol.line_id = p_so_line_id
76         and  ol.header_id = oh.header_id
77         and  oh.order_type_id = ot.transaction_type_id
78         and  mtl.inventory_item_id = ol.inventory_item_id;
79 
80    CURSOR Get_batch_info(p_batch_id IN NUMBER) IS
81       SELECT batch_no,plant_code,
82              plan_cmplt_date
83         FROM gme_batch_header
84         WHERE batch_id = p_batch_id;
85 
86    CURSOR Get_batch_line_info(p_batch_line_id IN NUMBER) IS
87       SELECT line_no
88         FROM gme_material_details
89         WHERE material_detail_id = p_batch_line_id;
90 
91     Cursor get_org_code (p_organization_id in NUMBER) is
92        Select organization_code
93        From mtl_parameters
94        Where organization_id = p_organization_id;
95 
96 BEGIN
97   IF g_debug <= gme_debug.g_log_procedure THEN
98      gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
99      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': Input Parameters......');
100      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': P_itemkey = '||p_itemkey);
101      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': P_itemtype = '||p_itemtype);
102      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': p_so_line_id   = '||p_so_line_id);
103      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': p_action_code  = '||p_action_code);
104      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': p_batch_id  = ' ||p_batch_id);
105      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': p_batch_line_id = '|| p_batch_line_id);
106      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': p_organization_id = '|| p_organization_id);
107      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': p_lot_no = '|| p_lot_no);
108 
109   END IF;
110      wf_log_pkg.wf_debug_flag:=TRUE;
111 
112   Open get_org_code(p_organization_id);
113   Fetch get_org_code Into l_organization_code;
114   Close get_org_code;
115   IF g_debug <= gme_debug.g_log_procedure THEN
116      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': l_organization_code = '|| l_organization_code);
117   END IF;
118 
119   /* Get the User Name */
120   OPEN Get_user_name(p_approver);
121   FETCH Get_user_name INTO l_approver_name;
122   IF(Get_user_name%NOTFOUND) THEN
123      IF g_debug <= gme_debug.g_log_procedure THEN
124         gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ': Get_user_name%NOTFOUND');
125      END IF;
126     CLOSE Get_user_name;
127     RETURN;
128   END IF;
129   CLOSE Get_user_name;
130   IF g_debug <= gme_debug.g_log_procedure THEN
131      gme_debug.put_line(g_pkg_name||'.'||l_api_name|| 'after get_user_name');
132   END IF;
133 
134   /* Get the Order and Line Information */
135   OPEN Get_order_info(p_so_line_id);
136   FETCH Get_order_info INTO l_so_line_no,l_item_no,
137         l_order_type,l_order_no;
138   IF(Get_order_info%NOTFOUND) THEN
139      IF g_debug <= gme_debug.g_log_procedure THEN
140         gme_debug.put_line(g_pkg_name||'.'||l_api_name ||'Get_order_info%NOTFOUND');
141      END IF;
142     CLOSE Get_order_info;
143     RETURN;
144   END IF;
145 
146   CLOSE Get_order_info;
147 
148   IF g_debug <= gme_debug.g_log_procedure THEN
149      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Order/Line Information......');
150      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Order_type = '|| l_order_type);
151      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Order_no   = '|| l_order_no);
152      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Line_no    = '|| l_so_line_no);
153      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Item_no    = '|| l_item_no);
154      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'after get_order_info');
155   END IF;
156 
157   /* Get the batch Information */
158   OPEN Get_batch_info(p_batch_id);
159   FETCH Get_batch_info INTO l_batch_no, l_plant_code,l_plan_complt_date;
160   IF(Get_batch_info%NOTFOUND) THEN
161      IF g_debug <= gme_debug.g_log_procedure THEN
162         gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Get_batch_info%NOTFOUND');
163      END IF;
164     CLOSE Get_batch_info;
165     --RETURN;
166   END IF;
167   CLOSE Get_batch_info;
168   l_fpo_no := l_batch_no;
169 
170   if p_batch_line_id is not null then
171      OPEN Get_batch_line_info(p_batch_line_id);
172      FETCH Get_batch_line_info INTO l_batch_line_no;
173      IF(Get_batch_line_info%NOTFOUND) THEN
174         IF g_debug <= gme_debug.g_log_procedure THEN
175            gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Get_batch_line_info%NOTFOUND');
176         END IF;
177        CLOSE Get_batch_line_info;
178        --RETURN;
179      END IF;
180      CLOSE Get_batch_line_info;
181   end if;
182 
183   if p_fpo_id is not null then
184     OPEN Get_batch_info(p_fpo_id);
185     FETCH Get_batch_info INTO l_fpo_no, l_plant_code,l_plan_complt_date;
186     IF(Get_batch_info%NOTFOUND) THEN
187         IF g_debug <= gme_debug.g_log_procedure THEN
188            gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Get_batch_info%NOTFOUND');
189         END IF;
190        CLOSE Get_batch_info;
191        --RETURN;
192     END IF;
193     CLOSE Get_batch_info;
194   end if;
195 
196 
197   IF g_debug <= gme_debug.g_log_procedure THEN
198      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Batch  Information......');
199      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Batch_no = '|| l_batch_no);
200      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Batch_line_no = '|| l_batch_line_no);
201      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; FPO_no = '|| l_FPO_no);
202      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Plant_code   = '|| l_plant_code);
203      gme_debug.put_line(g_pkg_name||'.'||l_api_name || '; Planned Completion Date    = '|| l_plan_complt_date);
204   END IF;
205   /* Increment the itemkey global variable */
206   g_itemkey_num := g_itemkey_num+1;
207   l_itemkey := l_itemkey||'-'||to_char(g_itemkey_num);
208 
209   /* create the process*/
210   /*IF g_debug <= gme_debug.g_log_procedure THEN
211      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Init_wf before CreateProcess');
212   END IF;
213   WF_ENGINE.CREATEPROCESS(
214                            itemtype => l_itemtype
215                          , itemkey => l_itemkey
216                          , process => l_WorkflowProcess) ;
217    */
218   /* make sure that process runs with background engine */
219   IF g_debug <= gme_debug.g_log_procedure THEN
220      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'after CreateProcess');
221   END IF;
222 
223  --  WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
224  --WF_ENGINE.THRESHOLD := -1 ;
225 
226   l_itemkey := p_itemkey;
227   l_itemtype := p_itemtype;
228   /* set the item attributes*/
229   WF_ENGINE.SETITEMATTRTEXT(
230                             itemtype => l_itemtype
231                          , itemkey =>l_itemkey
232                          , aname => 'APPROVER'
233                          , avalue => l_approver_name);
234   IF g_debug <= gme_debug.g_log_procedure THEN
235      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set Approver Name');
236   END IF;
237   WF_ENGINE.SETITEMATTRTEXT(
238                               itemtype => l_itemtype
239                             , itemkey => l_itemkey
240                             , aname => 'ACTION_CODE'
241                             , avalue => p_action_code );
242 
243   IF g_debug <= gme_debug.g_log_procedure THEN
244      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set Action Code ');
245   END IF;
246   WF_ENGINE.SETITEMATTRTEXT(
247                               itemtype => l_itemtype
248                             , itemkey => l_itemkey
249                             , aname => 'ORDER_NO'
250                             , avalue => to_char(l_order_no) );
251 
252   IF g_debug <= gme_debug.g_log_procedure THEN
253      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set Order Number ');
254   END IF;
255   WF_ENGINE.SETITEMATTRTEXT(
256                               itemtype => l_itemtype
257                              , itemkey => l_itemkey
258                              , aname => 'ORDER_TYPE'
259                              , avalue => l_order_type );
260 
261   IF g_debug <= gme_debug.g_log_procedure THEN
262      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set Order Type ');
263   END IF;
264   WF_ENGINE.SETITEMATTRTEXT(
265                               itemtype => l_itemtype
266                             , itemkey => l_itemkey
267                             , aname => 'SO_LINE_NO'
268                             , avalue => to_char(l_so_line_no) );
269 
270   IF g_debug <= gme_debug.g_log_procedure THEN
271      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set so_line_no ');
272   END IF;
273   WF_ENGINE.SETITEMATTRTEXT(
274                               itemtype => l_itemtype
275                             , itemkey => l_itemkey
276                             , aname => 'ITEM_NO'
277                             , avalue => l_item_no );
278 
279   WF_ENGINE.SETITEMATTRTEXT(
280                               itemtype => l_itemtype
281                             , itemkey => l_itemkey
282                             , aname => 'ORG_CODE'
283                             , avalue => l_organization_code);
284   IF g_debug <= gme_debug.g_log_procedure THEN
285      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set Batch Number ');
286   END IF;
287   WF_ENGINE.SETITEMATTRTEXT(
288                               itemtype => l_itemtype
289                             , itemkey => l_itemkey
290                             , aname => 'BATCH_NO'
291                             , avalue => l_batch_no );
292 
293   IF g_debug <= gme_debug.g_log_procedure THEN
294      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set Batch Line Number ');
295   END IF;
296   WF_ENGINE.SETITEMATTRTEXT(
297                               itemtype => l_itemtype
298                             , itemkey => l_itemkey
299                             , aname => 'BATCH_LINE_NO'
300                             , avalue => l_batch_line_no );
301 
302   IF (p_lot_no is NOT NULL) THEN
303      IF g_debug <= gme_debug.g_log_procedure THEN
304         gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set lot_no ');
305      END IF;
306      WF_ENGINE.SETITEMATTRTEXT(
307                               itemtype => l_itemtype
308                             , itemkey  => l_itemkey
309                             , aname    => 'LOT_NO'
310                             , avalue   => p_lot_no );
311   END IF;
312   IF g_debug <= gme_debug.g_log_procedure THEN
313      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Set FPO Number ');
314   END IF;
315   WF_ENGINE.SETITEMATTRTEXT(
316                               itemtype => l_itemtype
317                             , itemkey => l_itemkey
318                             , aname => 'FPO_NO'
319                             , avalue => l_fpo_no );
320   IF g_debug <= gme_debug.g_log_procedure THEN
321      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'after Setting the Attributes');
322      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Before Start Process');
323   END IF;
324   /*WF_ENGINE.STARTPROCESS (
325                             itemtype => l_itemtype
326                          , itemkey => l_itemkey );
327   */
328   IF g_debug <= gme_debug.g_log_procedure THEN
329      gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'After Start Process');
330   END IF;
331 
332 EXCEPTION
333 
334    WHEN OTHERS THEN
335       NULL;
336       IF g_debug <= gme_debug.g_log_procedure THEN
337          gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'WARNING! In GME_BATCH_WORKFLOW_PKG. Init_wf Others Exception');
338       END IF;
339 
340       WF_CORE.CONTEXT ('GME_BATCH_WORKFLOW','init_wf'
341 			,l_itemtype,l_itemkey,'Initial' );
342 
343       RAISE;
344 
345 END INIT_WF;
346 
347 PROCEDURE check_event(
348       p_itemtype      IN VARCHAR2,
349       p_itemkey       IN VARCHAR2,
350       p_actid         IN NUMBER,
351       p_funcmode      IN VARCHAR2,
352       p_resultout     OUT NOCOPY VARCHAR2) IS
353 
354     l_event_type VARCHAR2(240) := WF_ENGINE.GETITEMATTRTEXT( itemtype=>p_itemtype, itemkey=>P_itemkey, aname => 'ACTION_CODE');
355     --l_session_id         number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SESSION_ID');
356     l_approver           number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
357     l_so_header_id       number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SO_HEADER_ID');
358     l_so_line_id         number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SO_LINE_ID');
359     l_batch_id           number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'BATCH_ID');
360     l_batch_line_id      number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'BATCH_LINE_ID');
361     l_fpo_id             number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'FPO_ID');
362     l_organization_id    number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'ORGANIZATION_ID');
363     l_lot_no             varchar2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'LOT_NO');
364     l_action_code        varchar2(30) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'ACTION_CODE');
365 BEGIN
366 
367   wf_log_pkg.wf_debug_flag:=TRUE;
368   IF g_debug <= gme_debug.g_log_procedure THEN
369      gme_debug.put_line ('Entering check event ' );
370   End if;
371     init_wf (
372         p_itemtype           => p_itemtype
373       , p_itemkey            => p_itemkey
374       , p_approver           => l_approver
375       , p_so_header_id       => l_so_header_id
376       , p_so_line_id         => l_so_line_id
377       , p_batch_id           => l_batch_id
378       , p_batch_line_id      => l_batch_line_id
379       , p_fpo_id             => l_fpo_id
380       , p_organization_id    => l_organization_id
381       , p_lot_no             => l_lot_no
382       , p_action_code        => l_action_code
383       );
384 
385       p_resultout := 'COMPLETE:'||l_event_type;
386 
387 EXCEPTION
388 
389   WHEN OTHERS THEN
390      NULL;
391 
392 END check_event;
393 
394 
395 END GME_BATCH_WKFL_PKG;