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

Source


1 PACKAGE BODY GML_BATCH_WORKFLOW_PKG AS
2 /*  $Header: GMLBTWFB.pls 120.0 2005/05/25 16:47:05 appldev noship $  */
3 /* +=========================================================================+
4  |                Copyright (c) 2002 Oracle Corporation                    |
5  |                         All righTs reserved                             |
6  +=========================================================================+
7  | FILENAME                                                                |
8  |    GMLBTWFB.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 
24 PROCEDURE init_wf (
25       	p_session_id	IN   NUMBER
26       ,	p_approver   	IN   NUMBER
27       , p_so_header_id  IN   NUMBER
28       ,	p_so_line_id	IN   NUMBER
29       ,	p_batch_id	IN   NUMBER
30       , p_batch_line_id IN   NUMBER
31       , p_whse_code     IN   VARCHAR2
32       , p_lot_no	IN   VARCHAR2 DEFAULT NULL
33       ,	p_action_code   IN   VARCHAR2 )IS
34 
35    l_itemtype		VARCHAR2(240) :=  'GMLBTRES';
36    l_itemkey		VARCHAR2(240) := to_char(p_session_id)||'-'||to_char(sysdate,'dd-MON-yyyy HH24:mi:ss');
37    l_run_wf_in_background  CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
38    l_WorkflowProcess 	VARCHAR2(30)  := 'GMLBTRES_PROCESS';
39    l_count 		NUMBER;
40 
41    l_order_no		NUMBER;
42    l_so_line_no		NUMBER;
43    l_item_no 		VARCHAR2(40);
44    l_order_type		VARCHAR2(30);
45    l_batch_no		VARCHAR2(32);
46    l_fpo_no		VARCHAR2(32);
47    l_plant_code 	VARCHAR2(4);
48    l_plan_complt_date   DATE;
49    l_approver_name	VARCHAR2(20);
50 
51    /* Cusor Declarations */
52 
53    CURSOR  Get_user_name(p_user_id IN NUMBER) IS
54       SELECT user_name
55         FROM fnd_user
56        WHERE user_id = p_user_id;
57 
58    CURSOR Get_order_info(p_so_line_id IN NUMBER) IS
59       SELECT  ol.line_number, mtl.segment1,
60               ot.transaction_type_code,
61               oh.order_number
62         FROM  oe_order_headers_all oh
63              ,oe_order_lines_all ol
64      	     ,oe_transaction_types_all ot
65 	     ,mtl_system_items mtl
66        WHERE  ol.line_id = p_so_line_id
67          and  ol.header_id = oh.header_id
68 	 and  oh.order_type_id = ot.transaction_type_id
69          and  mtl.inventory_item_id = ol.inventory_item_id;
70 
71     CURSOR Get_batch_info(p_batch_id IN NUMBER) IS
72        SELECT batch_no,plant_code,
73               plan_cmplt_date
74          FROM gme_batch_header
75          WHERE batch_id = p_batch_id;
76 
77 
78 BEGIN
79 
80 
81    /* Get the User Name */
82 
83    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf workflow procedure');
84 
85    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: Input Parameters......');
86    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: P_so_header_id = '||p_so_header_id);
87    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: p_so_line_id   = '||p_so_line_id);
88    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: p_action_code  = '||p_action_code);
89    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: p_batch_id  = ' ||p_batch_id);
90    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: p_batch_line_id = '|| p_batch_line_id);
91    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: p_whse_code = '|| p_whse_code);
92    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf: p_lot_no = '|| p_lot_no);
93 
94    OPEN Get_user_name(p_approver);
95    FETCH Get_user_name INTO l_approver_name;
96    IF(Get_user_name%NOTFOUND) THEN
97      GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG.init_wf Get_user_name%NOTFOUND');
98      CLOSE Get_user_name;
99      RETURN;
100    END IF;
101 
102    CLOSE Get_user_name;
103 
104    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf after get_user_name');
105 
106    /* Get the Order and Line Information */
107 
108    OPEN Get_order_info(p_so_line_id);
109    FETCH Get_order_info INTO l_so_line_no,l_item_no,
110          l_order_type,l_order_no;
111    IF(Get_order_info%NOTFOUND) THEN
112      GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG.init_wf Get_order_info%NOTFOUND');
113      CLOSE Get_order_info;
114      RETURN;
115    END IF;
116 
117    CLOSE Get_order_info;
118 
119    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Order/Line Information......');
120    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Order_type = '|| l_order_type);
121    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Order_no   = '|| l_order_no);
122    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Line_no    = '|| l_so_line_no);
123    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Item_no    = '|| l_item_no);
124 
125    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf after get_order_info');
126 
127    /* Get the batch Information */
128 
129    OPEN Get_batch_info(p_batch_id);
130    FETCH Get_batch_info INTO l_batch_no, l_plant_code,l_plan_complt_date;
131    IF(Get_batch_info%NOTFOUND) THEN
132      GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG.init_wf Get_batch_info%NOTFOUND');
133      CLOSE Get_batch_info;
134      RETURN;
135    END IF;
136    CLOSE Get_batch_info;
137 
138    l_fpo_no := l_batch_no;
139 
140    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Batch  Information......');
141    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Batch_no = '|| l_batch_no);
142    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Plant_code   = '|| l_plant_code);
143    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf; Planned Completion Date    = '|| l_plan_complt_date);
144 
145    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf after Get_batch_info');
146 
147    /* Increment the itemkey global variable */
148 
149    g_itemkey_num := g_itemkey_num+1;
150    l_itemkey := l_itemkey||'-'||to_char(g_itemkey_num);
151 
152    /* create the process*/
153 
154 
155    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf before CreateProcess');
156 
157    WF_ENGINE.CREATEPROCESS(
158 			  itemtype => l_itemtype
159 			, itemkey => l_itemkey
160 			, process => l_WorkflowProcess) ;
161 
162    /* make sure that process runs with background engine */
163 
164    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf after CreateProcess');
165 
166 --  WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
167  --WF_ENGINE.THRESHOLD := -1 ;
168 
169    /* set the item attributes*/
170    /*
171    WF_ENGINE.SETITEMATTRNUMBER(
172 			  itemtype => l_itemtype
173 			, itemkey => l_itemkey
174         		, aname => 'SESSION_ID'
175 			, avalue => l_session_id );
176    */
177 
178    WF_ENGINE.SETITEMATTRTEXT(
179 			  itemtype => l_itemtype
180 			, itemkey =>l_itemkey
181 			, aname => 'APPROVER'
182 			, avalue => l_approver_name);
183 
184    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Approver Name');
185 
186    WF_ENGINE.SETITEMATTRTEXT(
187 			  itemtype => l_itemtype
188 			, itemkey => l_itemkey
189         		, aname => 'ACTION_CODE'
190 			, avalue => p_action_code );
191 
192    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set action Code');
193 
194    WF_ENGINE.SETITEMATTRTEXT(
195 			  itemtype => l_itemtype
196 			, itemkey => l_itemkey
197         		, aname => 'ORDER_NO'
198 			, avalue => to_char(l_order_no) );
199 
200    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Order Number ');
201 
202    WF_ENGINE.SETITEMATTRTEXT(
203 			  itemtype => l_itemtype
204 			, itemkey => l_itemkey
205         		, aname => 'ORDER_TYPE'
206 			, avalue => l_order_type );
207 
208    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Order Type ');
209 
210    WF_ENGINE.SETITEMATTRTEXT(
211 			  itemtype => l_itemtype
212 			, itemkey => l_itemkey
213         		, aname => 'SO_LINE_NO'
214 			, avalue => to_char(l_so_line_no) );
215 
216    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set so_line_no ');
217 
218    WF_ENGINE.SETITEMATTRTEXT(
219 			  itemtype => l_itemtype
220 			, itemkey => l_itemkey
221         		, aname => 'ITEM_NO'
222 			, avalue => l_item_no );
223 
224    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Item Number ');
225 
226    WF_ENGINE.SETITEMATTRTEXT(
227 			  itemtype => l_itemtype
228 			, itemkey => l_itemkey
229         		, aname => 'PLANT_CODE'
230 			, avalue => l_plant_code );
231 
232    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Plant Code ');
233 
234    WF_ENGINE.SETITEMATTRTEXT(
235 			  itemtype => l_itemtype
236 			, itemkey => l_itemkey
237         		, aname => 'BATCH_NO'
238 			, avalue => l_batch_no );
239 
240    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Whse Code ');
241 
242    WF_ENGINE.SETITEMATTRTEXT(
243 			  itemtype => l_itemtype
244 			, itemkey => l_itemkey
245         		, aname => 'WHSE_CODE'
246 			, avalue => p_whse_code );
247 
248    IF (p_lot_no is NOT NULL) THEN
249       GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Whse Code ');
250       WF_ENGINE.SETITEMATTRTEXT(
251 			  itemtype => l_itemtype
252 			, itemkey  => l_itemkey
253         		, aname    => 'LOT_NO'
254 			, avalue   => p_lot_no );
255    END IF;
256 
257    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set Batch Number ');
258 
259    WF_ENGINE.SETITEMATTRTEXT(
260 			  itemtype => l_itemtype
261 			, itemkey => l_itemkey
262         		, aname => 'FPO_NO'
263 			, avalue => l_fpo_no );
264 
265    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Set FPO Number ');
266 
267   /*
268    WF_ENGINE.SETITEMATTRTEXT(
269 			  itemtype => l_itemtype
270 			, itemkey => l_itemkey
271         		, aname => 'PL_COMPL_DATE'
272 			, avalue => TO_CHAR(TO_DATE(l_plan_complt_date,'MON-DD-YYYY')) );
273    */
274 
275   /* start the Workflow process */
276 
277    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf after Setting the Attributes');
278    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Before Start Process');
279 
280    WF_ENGINE.STARTPROCESS (
281 			  itemtype => l_itemtype
282 			, itemkey => l_itemkey );
283 
284    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf After Start Process');
285 
286    /* Insert the workflow/batch/so information into Gml_batch_so_workflow table for
287       future reference. */
288 
289    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Before Insert Record');
290 
291    INSERT_GML_BATCH_SO_WORKFLOW(
292                          l_itemtype
293                        , l_itemkey
294 		       , p_so_header_id
295                        , p_so_line_id
296                        , p_batch_id
297 		       , p_batch_line_id
298 		       , p_action_code);
299 
300    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf After  Insert Record');
301 
302    GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. Init_wf Exiting ....');
303 
304 
305 EXCEPTION
306 
307    WHEN OTHERS THEN
308       NULL;
309       GMI_RESERVATION_UTIL.PrintLn('WARNING! In GML_BATCH_WORKFLOW_PKG. Init_wf Others Exception');
310 
311       WF_CORE.CONTEXT ('GML_BATCH_WORKFLOW','init_wf'
312 			,l_itemtype,l_itemkey,'Initial' );
313 
314       RAISE;
315 
316 END INIT_WF;
317 
318 PROCEDURE check_event(
319       p_itemtype      IN VARCHAR2,
320       p_itemkey       IN VARCHAR2,
321       p_actid         IN NUMBER,
322       p_funcmode      IN VARCHAR2,
323       p_resultout     OUT NOCOPY VARCHAR2) IS
324 
325    l_event_type VARCHAR2(240) := WF_ENGINE.GETITEMATTRTEXT(
326                                                 itemtype=>p_itemtype,
327                                                 itemkey=>P_itemkey,
328                                                 aname => 'ACTION_CODE');
329 BEGIN
330 
331    p_resultout := 'COMPLETE:'||l_event_type;
332 
333 EXCEPTION
334 
335   WHEN OTHERS THEN
336      NULL;
337 
338 END check_event;
339 
340 
341 PROCEDURE INSERT_GML_BATCH_SO_WORKFLOW(
342              p_itemtype     IN 	VARCHAR2
343            , p_itemkey      IN	VARCHAR2
344            , p_so_header_id IN	NUMBER
345            , p_so_line_id   IN	NUMBER
346            , p_batch_id	    IN	NUMBER
347            , p_batch_line_id IN	NUMBER
348            , p_action_code   IN	VARCHAR2) IS
349 
350    l_wf_item_id  NUMBER;
351 
352 BEGIN
353 
354   GMI_RESERVATION_UTIL.PrintLn('In GML_BATCH_WORKFLOW_PKG. INSERT_GML_BATCH_SO_WORKFLOW..... ');
355 
356   SELECT GML_BATCH_SO_WORKFLOW_S.nextval
357     INTO l_wf_item_id FROM DUAL;
358 
359   GMI_RESERVATION_UTIL.PrintLn('In  GML_BATCH_WORKFLOW_PKG. INSERT_GML_BATCH_SO_WORKFLOW Before Insert ');
360 
361   INSERT INTO Gml_batch_so_workflow (
362 		  WF_ITEM_ID
363    		, WF_ITEM_TYPE
364    		, WF_ITEM_KEY
365    		, SO_HEADER_ID
366    		, SO_LINE_ID
367    		, BATCH_ID
368    		, BATCH_LINE_ID
369    		, ACTION_CODE
370    		, CREATION_DATE
371    		, CREATED_BY
372    		, LAST_UPDATE_DATE
373    		, LAST_UPDATED_BY ) VALUES
374 
375                 ( l_wf_item_id
376 		, p_itemtype
377                 , p_itemkey
378                 , p_so_header_id
379                 , p_so_line_id
380                 , p_batch_id
381                 , p_batch_line_id
382                 , p_action_code
383                 , sysdate
384                 , fnd_global.user_id
385                 , sysdate
386                 , fnd_global.user_id);
387 
388   GMI_RESERVATION_UTIL.PrintLn('In  GML_BATCH_WORKFLOW_PKG. INSERT_GML_BATCH_SO_WORKFLOW After Insert ');
389 
390 EXCEPTION
391 
392   WHEN OTHERS THEN
393     GMI_RESERVATION_UTIL.PrintLn('WARNING!  GML_BATCH_WORKFLOW_PKG. INSERT_GML_BATCH_SO_WORKFLOW OTHERS Exception ');
394     NULL;
395 
396 END INSERT_GML_BATCH_SO_WORKFLOW;
397 
398 
399 END GML_BATCH_WORKFLOW_PKG;