[Home] [Help]
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;