[Home] [Help]
PACKAGE BODY: APPS.GMI_APIXML_PKG
Source
1 PACKAGE BODY gmi_apixml_pkg AS
2 /* $Header: GMIXAPIB.pls 115.13 2002/12/06 15:26:16 jdiiorio noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMI_APIXML_PKG';
5
6 PROCEDURE log_message( p_proc_name IN VARCHAR2,
7 p_if_id IN NUMBER,
8 p_msg IN VARCHAR2);
9
10 PROCEDURE update_status(p_proc_name IN VARCHAR2,
11 p_if_id IN NUMBER,
12 p_err_msg IN VARCHAR2);
13
14 PROCEDURE send_outbound_document( p_confirm_statuslvl IN VARCHAR2,
15 p_confirm_descrtn IN VARCHAR2,
16 p_confirm_det_descriptn IN VARCHAR2,
17 p_confirm_det_reasoncode IN VARCHAR2,
18 p_icn IN NUMBER,
19 p_event_key IN VARCHAR2);
20
21 PROCEDURE api_selector ( item_type IN VARCHAR2,
22 item_key IN VARCHAR2,
23 actid IN NUMBER,
24 command IN VARCHAR2,
25 resultout IN OUT NOCOPY VARCHAR2 ) IS
26
27 l_item_int_id VARCHAR2(240);
28 l_lot_int_id VARCHAR2(240);
29 l_conv_int_id VARCHAR2(240);
30
31 l_txn_type VARCHAR2(240);
32 l_param1 VARCHAR2(4000);
33 l_param2 VARCHAR2(4000);
34 l_param3 VARCHAR2(4000);
35 l_param4 VARCHAR2(4000);
36 l_param5 VARCHAR2(4000);
37
38 -- local variables for setting the wf context
39 l_user_id NUMBER;
40 l_user_name fnd_user.user_name%TYPE;
41 l_resp_appl_id NUMBER;
42 l_resp_id NUMBER;
43 l_org_id NUMBER;
44 l_language VARCHAR2(240);
45 l_nls_lang_find NUMBER;
46 l_nls_lang VARCHAR2(255);
47 l_user_key VARCHAR2(255);
48 l_role_name VARCHAR2(255);
49 l_role_display_name VARCHAR2(255);
50
51 l_item_number VARCHAR2(32);
52 l_lot_number GMI_LOTS_XML_INTERFACE.LOT_NUMBER%TYPE;
53 l_ext_lot_id GMI_LOTS_XML_INTERFACE.EXT_LOT_ID%TYPE;
54 l_ext_conv_id GMI_LOTS_CONV_XML_INTERFACE.EXT_CONV_ID%TYPE;
55
56 --Cursors to fetch the user name from the interface record.
57 CURSOR c_qty_user_name(p_qty_if_id VARCHAR2) IS
58 SELECT user_name, item_number
59 FROM gmi_quantity_xml_interface
60 WHERE quantity_interface_id = TO_NUMBER(p_qty_if_id);
61
62 CURSOR c_item_user_name(p_item_if_id VARCHAR2) IS
63 SELECT user_name, item_number
64 FROM gmi_items_xml_interface
65 WHERE item_interface_id = TO_NUMBER(p_item_if_id);
66
67 CURSOR c_lot_user_name(p_lot_if_id VARCHAR2) IS
68 SELECT user_name, item_number, lot_number, ext_lot_id
69 FROM gmi_lots_xml_interface
70 WHERE lot_interface_id = TO_NUMBER(p_lot_if_id);
71
72 CURSOR c_conv_user_name(p_conv_if_id VARCHAR2) IS
73 SELECT user_name, item_number, lot_number, ext_conv_id
74 FROM gmi_lots_conv_xml_interface
75 WHERE conv_interface_id = TO_NUMBER(p_conv_if_id);
76
77 BEGIN
78
79 IF( command = 'RUN' )
80 THEN
81 -- Retrieve the attributes
82 l_param1 := wf_engine.GetItemAttrText(
83 itemtype => item_type,
84 itemkey => item_key,
85 aname => 'PARAMETER1');
86
87
88 l_param2 := wf_engine.GetItemAttrText(
89 itemtype => item_type,
90 itemkey => item_key,
91 aname => 'PARAMETER2');
92
93 IF l_param1 = 'GMI' AND l_param2 = 'QTY' THEN --ITEM_TYPE = 'GMIQTAPI' THEN
94
95 resultout := 'CREATE_TRANSACTION';
96
97 ELSIF l_param1 = 'GMI' AND l_param2 = 'ITEM' THEN --ITEM_TYPE = 'GMIICAPI' THEN
98
99 resultout := 'CREATE_ITEM';
100
101 ELSIF l_param1 = 'GMI' AND l_param2 = 'LOT' THEN --ITEM_TYPE = 'GMILTAPI' THEN
102
103 resultout := 'CREATE_LOT'; -- process name
104
105 ELSIF l_param1 = 'GMI' AND l_param2 = 'ITMCV' THEN --ITEM_TYPE = 'GMILCAPI' THEN
106
107 resultout := 'CREATE_LTCONV'; -- process name
108 END IF;
109
110 ELSIF( command = 'SET_CTX' )
111 THEN
112 -- Retrieve the attributes
113 l_param1 := wf_engine.GetItemAttrText(
114 itemtype => item_type,
115 itemkey => item_key,
116 aname => 'PARAMETER1');
117
118 l_param2 := wf_engine.GetItemAttrText(
119 itemtype => item_type,
120 itemkey => item_key,
121 aname => 'PARAMETER2');
122
123 l_param3 := wf_engine.GetItemAttrText(
124 itemtype => item_type,
125 itemkey => item_key,
126 aname => 'PARAMETER3');
127
128 l_param4 := wf_engine.GetItemAttrText(
129 itemtype => item_type,
130 itemkey => item_key,
131 aname => 'PARAMETER4');
132
133 l_param5 := wf_engine.GetItemAttrText(
134 itemtype => item_type,
135 itemkey => item_key,
136 aname => 'PARAMETER5');
137
138 /**
139 * Get the User Id and Item Number
140 * We cannot use FND_GLOBAL.USER_ID since we do not
141 * set the apps context
142 */
143 l_user_name := NULL;
144 l_item_number := NULL;
145 l_lot_number := NULL;
146 l_ext_lot_id := NULL;
147 l_ext_conv_id := NULL;
148
149
150 IF l_param1 = 'GMI' AND l_param2 = 'QTY' THEN --ITEM_TYPE = 'GMIQTAPI' THEN
151
152 -- Set the appropriate attributes
153 wf_engine.setItemAttrnumber(
154 itemtype => item_type,
155 itemkey => item_key,
156 aname => 'EXT_TRANSACTION_ID',
157 avalue => l_param3);
158
159 wf_engine.setItemAttrnumber(
160 itemtype => item_type,
161 itemkey => item_key,
162 aname => 'GMI_QUANTITY_INTERFACE_ID',
163 avalue => l_param4);
164
165 wf_engine.SetItemAttrText(
166 itemtype => item_type,
167 itemkey => item_key,
168 aname => 'GMI_TRANSACTION_TYPE',
169 avalue => l_param5);
170
171
172 OPEN c_qty_user_name(l_param4);
173 FETCH c_qty_user_name INTO l_user_name, l_item_number;
174 CLOSE c_qty_user_name;
175
176 GMA_GLOBAL_GRP.Get_who(
177 p_user_name => l_user_name,
178 x_user_id => l_user_id);
179
180 -- User Key = Transaction_type.Item_number.Ext_transaction_id
181 l_user_key := l_param5 || '.' || l_item_number || '.' || l_param3 ;
182
183 ELSIF l_param1 = 'GMI' AND l_param2 = 'ITEM' THEN --ITEM_TYPE = 'GMIICAPI' THEN
184
185 -- Set the appropriate attributes
186 wf_engine.SetItemAttrText(
187 itemtype => item_type,
188 itemkey => item_key,
189 aname => 'EXT_ITEM_ID',
190 avalue => l_param3);
191
192 wf_engine.setItemAttrnumber(
193 itemtype => item_type,
194 itemkey => item_key,
195 aname => 'GMI_ITEM_INTERFACE_ID',
196 avalue => l_param4);
197
198 OPEN c_item_user_name(l_param4);
199 FETCH c_item_user_name INTO l_user_name, l_item_number;
200 CLOSE c_item_user_name;
201
202 GMA_GLOBAL_GRP.Get_who(
203 p_user_name => l_user_name,
204 x_user_id => l_user_id);
205
206 -- User Key = Item_number.Ext_item_id
207 l_user_key := l_item_number || '.' || l_param3 ;
208
209
210 ELSIF l_param1 = 'GMI' AND l_param2 = 'LOT' THEN --ITEM_TYPE = 'GMILTAPI' THEN
211
212 -- Set the appropriate attributes
213 wf_engine.SetItemAttrText(
214 itemtype => item_type,
215 itemkey => item_key,
216 aname => 'EXT_LOT_ID',
217 avalue => l_param3);
218
219 wf_engine.setItemAttrnumber(
220 itemtype => item_type,
221 itemkey => item_key,
222 aname => 'GMI_LOT_INTERFACE_ID',
223 avalue => l_param4);
224
225 OPEN c_lot_user_name(l_param4);
226 FETCH c_lot_user_name INTO l_user_name, l_item_number,
227 l_lot_number, l_ext_lot_id;
228 CLOSE c_lot_user_name;
229
230 GMA_GLOBAL_GRP.Get_who(
231 p_user_name => l_user_name,
232 x_user_id => l_user_id);
233
234 -- User Key = Lot_no,item_no,Ext_trans_id
235 l_user_key := l_lot_number || '.' || l_item_number || '.' || l_ext_lot_id;
236
237 ELSIF l_param1 = 'GMI' AND l_param2 = 'ITMCV' THEN --ITEM_TYPE = 'GMILCAPI' THEN
238
239 -- Set the appropriate attributes
240 wf_engine.SetItemAttrText(
241 itemtype => item_type,
242 itemkey => item_key,
243 aname => 'EXT_CONV_ID',
244 avalue => l_param3);
245
246 wf_engine.setItemAttrnumber(
247 itemtype => item_type,
248 itemkey => item_key,
249 aname => 'CONV_INTERFACE_ID',
250 avalue => l_param4);
251
252 OPEN c_conv_user_name(l_param4);
253 FETCH c_conv_user_name INTO l_user_name, l_item_number, l_lot_number, l_ext_conv_id;
254 CLOSE c_conv_user_name;
255
256 GMA_GLOBAL_GRP.Get_who(
257 p_user_name => l_user_name,
258 x_user_id => l_user_id);
259
260 -- User Key = Lot_no,item_no,Ext_conv_id
261 l_user_key := l_lot_number || '.' || l_item_number || '.' || l_ext_conv_id;
262
263 END IF;
264
265 -- Set the user key
266 Wf_engine.setItemUserkey(itemtype => item_type,
267 itemkey => item_key,
268 userkey => l_user_key);
269
270 -- Set the other context variables
271 -- Default language context is already set by apps login
272
273 Wf_engine.SetItemAttrText(itemtype => item_type,
274 itemkey => item_key,
275 aname => 'NOTIFICATION_MESSAGE',
276 avalue => '');
277
278 Wf_directory.getRoleName(p_orig_system => 'FND_USR',
279 p_orig_system_id => l_user_id,
280 p_name => l_role_name,
281 p_display_name => l_role_display_name);
282
283 Wf_engine.setItemOwner(itemtype => item_type,
284 itemkey => item_key,
285 owner => l_role_name);
286
287 Wf_engine.setItemAttrText(itemtype => item_type,
288 itemkey => item_key,
289 aname => 'WF_ADMINISTRATOR',
290 avalue => l_role_name);
291
292
293
294 Wf_directory.getRoleName(p_orig_system => 'ECX_SA_ROLE',
295 p_orig_system_id => 0,
296 p_name => l_role_name,
297 p_display_name => l_role_display_name);
298
299 Wf_engine.setItemAttrText(itemtype => item_type,
300 itemkey => item_key,
301 aname => 'ECX_ADMINISTRATOR',
302 avalue => l_role_name);
303
304 ELSIF( command = 'TEST_CTX' )
305 THEN
306
307 -- Retrieve the attributes
308 l_param1 := wf_engine.GetItemAttrText(
309 itemtype => item_type,
310 itemkey => item_key,
311 aname => 'PARAMETER1');
312
313
314 l_param2 := wf_engine.GetItemAttrText(
315 itemtype => item_type,
316 itemkey => item_key,
317 aname => 'PARAMETER2');
318
319
320 IF l_param1 = 'GMI' AND l_param2 = 'QTY' THEN --ITEM_TYPE = 'GMIQTAPI' THEN
321
322 l_txn_type := wf_engine.GetItemAttrText(
323 itemtype => item_type,
324 itemkey => item_key,
325 aname => 'GMI_TRANSACTION_TYPE');
326
327 IF( l_txn_type IS NULL )
328 THEN
329 resultout := 'FALSE';
330 ELSE
331 resultout := 'TRUE';
332 END IF;
333
334 ELSIF l_param1 = 'GMI' AND l_param2 = 'ITEM' THEN --ITEM_TYPE = 'GMIICAPI' THEN
335
336 l_item_int_id := wf_engine.GetItemAttrText(
337 itemtype => item_type,
338 itemkey => item_key,
339 aname => 'GMI_ITEM_INTERFACE_ID');
340
341 IF( l_item_int_id IS NULL )
342 THEN
343 resultout := 'FALSE';
344 ELSE
345 resultout := 'TRUE';
346 END IF;
347
348 ELSIF l_param1 = 'GMI' AND l_param2 = 'LOT' THEN --ITEM_TYPE = 'GMILTAPI' THEN
349
350 l_lot_int_id := wf_engine.GetItemAttrText(
351 itemtype => item_type,
352 itemkey => item_key,
353 aname => 'EXT_LOT_ID');
354
355 IF( l_lot_int_id IS NULL )
356 THEN
357 resultout := 'FALSE';
358 ELSE
359 resultout := 'TRUE';
360 END IF;
361
362 ELSIF l_param1 = 'GMI' AND l_param2 = 'ITMCV' THEN --ITEM_TYPE = 'GMILCAPI' THEN
363
364 l_conv_int_id := wf_engine.GetItemAttrText(
365 itemtype => item_type,
366 itemkey => item_key,
367 aname => 'CONV_INTERFACE_ID');
368
369 IF( l_conv_int_id IS NULL )
370 THEN
371 resultout := 'FALSE';
372 ELSE
373 resultout := 'TRUE';
374 END IF;
375
376 END IF;
377
378 END IF;
379
380 END api_selector;
381
382 PROCEDURE process_transaction (
383 item_type IN VARCHAR2,
384 item_key IN VARCHAR2,
385 actid IN NUMBER,
386 funcmode IN VARCHAR2,
387 resultout IN OUT NOCOPY VARCHAR2 )
388 IS
389
390 l_interface_rec gmi_quantity_xml_interface%ROWTYPE;
391 l_trans_rec Gmigapi.qty_rec_typ;
392
393 l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
394 l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
395 l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
396
397 l_status VARCHAR2(1);
398 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
399 l_count NUMBER;
400 l_count_msg NUMBER;
401 l_data VARCHAR2(2000);
402 l_dummy_cnt NUMBER :=0;
403 l_record_count NUMBER :=0;
404
405 CURSOR c_if_rec(p_interface_id NUMBER, p_ext_txn_id NUMBER) IS
406 SELECT *
407 FROM gmi_quantity_xml_interface
408 WHERE quantity_interface_id = p_interface_id
409 AND ext_transaction_id = p_ext_txn_id;
410
411 l_qty_iface_id NUMBER(15);
412 l_ext_txn_id NUMBER(15);
413
414 e_txn_not_found EXCEPTION;
415 e_txn_failed EXCEPTION;
416
417 l_event_name VARCHAR2(100);
418 l_event_key VARCHAR2(100);
419 l_icn NUMBER;
420
421 l_confirm_statuslvl VARCHAR2(500);
422 l_confirm_descrtn VARCHAR2(500);
423
424 BEGIN
425 IF( funcmode = 'RUN' )
426 THEN
427 resultout := 'COMPLETE:Y';
428
429 -- Get the variables and call the api procedure
430 l_qty_iface_id := wf_engine.GetItemAttrNumber(
431 itemtype => item_type,
432 itemkey => item_key,
433 aname => 'GMI_QUANTITY_INTERFACE_ID');
434
435 l_ext_txn_id := wf_engine.GetItemAttrNumber(
436 itemtype => item_type,
437 itemkey => item_key,
438 aname => 'EXT_TRANSACTION_ID');
439
440 OPEN c_if_rec(l_qty_iface_id, l_ext_txn_id);
441 FETCH c_if_rec INTO l_interface_rec;
442 IF( c_if_rec%NOTFOUND )
443 THEN
444 CLOSE c_if_rec;
445 RAISE e_txn_not_found;
446 END IF;
447 CLOSE c_if_rec;
448
449 Wf_engine.SetItemAttrText(
450 itemtype => item_type,
451 itemkey => item_key,
452 aname => 'GMI_ITEM_NUMBER',
453 avalue => l_interface_rec.item_number);
454
455 SELECT DECODE(l_interface_rec.transaction_type,
456 'CREI', 1,
457 'ADJI', 2,
458 'TRNI', 3,
459 'STSI', 4,
460 'GRDI', 5,
461 'CRER', 6,
462 'ADJR', 7,
463 'TRNR', 8,
464 'STSR', 9,
465 'GRDR', 10,-1)
466 INTO l_trans_rec.trans_type
467 FROM dual;
468
469 l_trans_rec.item_no := l_interface_rec.item_number;
470 l_trans_rec.journal_no := l_interface_rec.journal_number;
471 l_trans_rec.from_whse_code := l_interface_rec.from_warehouse;
472 l_trans_rec.to_whse_code := l_interface_rec.to_warehouse;
473 l_trans_rec.item_um := l_interface_rec.primary_uom;
474 l_trans_rec.item_um2 := l_interface_rec.secondary_uom;
475 l_trans_rec.lot_no := l_interface_rec.lot_number;
476 l_trans_rec.sublot_no := l_interface_rec.sublot_number;
477 l_trans_rec.from_location := l_interface_rec.from_location;
478 l_trans_rec.to_location := l_interface_rec.to_location;
479 l_trans_rec.trans_qty := l_interface_rec.primary_trans_qty;
480 l_trans_rec.trans_qty2 := l_interface_rec.secondary_trans_qty;
481 l_trans_rec.qc_grade := l_interface_rec.qc_grade;
482 l_trans_rec.lot_status := l_interface_rec.lot_status;
483 l_trans_rec.co_code := l_interface_rec.co_code;
484 l_trans_rec.orgn_code := l_interface_rec.orgn_code;
485
486 l_trans_rec.attribute1 := l_interface_rec.ATTRIBUTE1;
487 l_trans_rec.attribute2 := l_interface_rec.ATTRIBUTE2;
488 l_trans_rec.attribute3 := l_interface_rec.ATTRIBUTE3;
489 l_trans_rec.attribute4 := l_interface_rec.ATTRIBUTE4;
490 l_trans_rec.attribute5 := l_interface_rec.ATTRIBUTE5;
491 l_trans_rec.attribute6 := l_interface_rec.ATTRIBUTE6;
492 l_trans_rec.attribute7 := l_interface_rec.ATTRIBUTE7;
493 l_trans_rec.attribute8 := l_interface_rec.ATTRIBUTE8;
494 l_trans_rec.attribute9 := l_interface_rec.ATTRIBUTE9;
495 l_trans_rec.attribute10 := l_interface_rec.ATTRIBUTE10;
496 l_trans_rec.attribute11 := l_interface_rec.ATTRIBUTE11;
497 l_trans_rec.attribute12 := l_interface_rec.ATTRIBUTE12;
498 l_trans_rec.attribute13 := l_interface_rec.ATTRIBUTE13;
499 l_trans_rec.attribute14 := l_interface_rec.ATTRIBUTE14;
500 l_trans_rec.attribute15 := l_interface_rec.ATTRIBUTE15;
501 l_trans_rec.attribute16 := l_interface_rec.ATTRIBUTE16;
502 l_trans_rec.attribute17 := l_interface_rec.ATTRIBUTE17;
503 l_trans_rec.attribute18 := l_interface_rec.ATTRIBUTE18;
504 l_trans_rec.attribute19 := l_interface_rec.ATTRIBUTE19;
505 l_trans_rec.attribute20 := l_interface_rec.ATTRIBUTE20;
506 l_trans_rec.attribute21 := l_interface_rec.ATTRIBUTE21;
507 l_trans_rec.attribute22 := l_interface_rec.ATTRIBUTE22;
508 l_trans_rec.attribute23 := l_interface_rec.ATTRIBUTE23;
509 l_trans_rec.attribute24 := l_interface_rec.ATTRIBUTE24;
510 l_trans_rec.attribute25 := l_interface_rec.ATTRIBUTE25;
511 l_trans_rec.attribute26 := l_interface_rec.ATTRIBUTE26;
512 l_trans_rec.attribute27 := l_interface_rec.ATTRIBUTE27;
513 l_trans_rec.attribute28 := l_interface_rec.ATTRIBUTE28;
514 l_trans_rec.attribute29 := l_interface_rec.ATTRIBUTE29;
515 l_trans_rec.attribute30 := l_interface_rec.ATTRIBUTE30;
516 l_trans_rec.attribute_category := l_interface_rec.ATTRIBUTE_CATEGORY;
517 l_trans_rec.acctg_unit_no := l_interface_rec.ACCTG_UNIT_NO;
518 l_trans_rec.acct_no := l_interface_rec.ACCT_NO;
519
520 IF( l_interface_rec.transaction_date IS NULL )
521 THEN
522 l_trans_rec.trans_date := SYSDATE;
523 ELSE
524 l_trans_rec.trans_date := TO_DATE(l_interface_rec.transaction_date,
525 'YYYY/MM/DD HH24:MI:SS');
526 END IF;
527
528 l_trans_rec.reason_code := l_interface_rec.reason_code;
529
530 IF( l_interface_rec.user_name IS NULL )
531 THEN
532 l_trans_rec.user_name := 'OPM';
533 ELSE
534 l_trans_rec.user_name := l_interface_rec.user_name;
535 END IF;
536
537 l_trans_rec.journal_comment := l_interface_rec.journal_comment;
538
539 -- Set the context for the GMI APIs
540 IF( NOT Gmigutl.Setup(l_interface_rec.user_name) )
541 THEN
542 RAISE e_txn_failed;
543 END IF;
544
545 -- Call the standard API and check the return status
546 Gmipapi.Inventory_Posting
547 ( p_api_version => 3.0
548 , p_init_msg_list => FND_API.G_TRUE
549 , p_commit => FND_API.G_FALSE
550 , p_validation_level => FND_API.G_valid_level_full
551 , p_qty_rec => l_trans_rec
552 , x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
553 , x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
554 , x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
555 , x_return_status => l_status
556 , x_msg_count => l_count
557 , x_msg_data => l_data
558 );
559
560 IF( l_status IN ('U','E') )
561 THEN
562 RAISE e_txn_failed;
563 ELSE
564 FOR l_loop_cnt IN 1..l_count
565 LOOP
566
567 FND_MSG_PUB.Get(
568 p_msg_index => l_loop_cnt,
569 p_data => l_data,
570 p_encoded => FND_API.G_FALSE,
571 p_msg_index_out => l_dummy_cnt);
572
573 -- write to log
574 /* log_message( p_proc_name => 'process_transaction',
575 p_if_id => l_qty_iface_id,
576 p_msg => l_data);*/
577
578 END LOOP;
579
580 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_SUCCESS');
581 l_confirm_statuslvl := FND_MESSAGE.get;
582
583 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_QTY_S');
584 l_confirm_descrtn := FND_MESSAGE.get;
585
586 l_icn := wf_engine.GetItemAttrNumber(
587 itemtype => item_type,
588 itemkey => item_key,
589 aname => 'PARAMETER7');
590
591 l_event_key := l_interface_rec.transaction_type||'.'||l_trans_rec.item_no||'.'||l_ext_txn_id;
592
593 send_outbound_document( l_confirm_statuslvl,
594 l_confirm_descrtn,
595 l_data,
596 l_confirm_statuslvl,
597 l_icn,
598 l_event_key);
599
600 DELETE FROM gmi_quantity_xml_interface
601 WHERE quantity_interface_id = l_qty_iface_id
602 AND ext_transaction_id = l_ext_txn_id;
603
604 resultout := 'COMPLETE:Y';
605 END IF;
606
607
608
609 /* Update error status */
610 l_return_status := l_status;
611
612 -- End of funcmode = RUN
613
614 ELSIF funcmode = 'CANCEL'
615 THEN
616 resultout := 'COMPLETE';
617
618 ELSIF funcmode = 'RESPOND'
619 THEN
620 resultout := 'COMPLETE';
621
622 ELSIF funcmode = 'FORWARD'
623 THEN
624 resultout := 'COMPLETE';
625
626 ELSIF funcmode = 'TRANSFER'
627 THEN
628 resultout := 'COMPLETE';
629
630 ELSIF funcmode = 'TIMEOUT'
631 THEN
632 resultout := 'COMPLETE';
633
634 ELSE
635 resultout := NULL;
636 END IF;
637
638 EXCEPTION
639 WHEN e_txn_not_found THEN
640 log_message(
641 p_proc_name => 'process_transaction',
642 p_if_id => l_qty_iface_id,
643 p_msg => 'Could not find the interface record for if id =>'||
644 l_qty_iface_id || ' ext id =>' || l_ext_txn_id);
645
646 resultout := 'COMPLETE:N';
647
648 RAISE;
649
650 WHEN e_txn_failed THEN
651 resultout := 'COMPLETE:N';
652 -- API Failed. Error message must be on stack.
653 l_count_msg := fnd_msg_pub.Count_Msg;
654
655 FOR l_loop_cnt IN 1..l_count_msg
656 LOOP
657 FND_MSG_PUB.GET(P_msg_index => l_loop_cnt,
658 P_data => l_data,
659 P_encoded => FND_API.G_FALSE,
660 P_msg_index_out => l_dummy_cnt);
661
662 log_message(p_proc_name => 'process_transaction',
663 p_if_id => l_qty_iface_id,
664 p_msg => 'Error :' || l_data);
665 END LOOP;
666
667
668 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_FAIL');
669 l_confirm_statuslvl := FND_MESSAGE.get;
670
671 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_QTY_F');
672 l_confirm_descrtn := FND_MESSAGE.get;
673
674 -- Set the appropriate attributes for confirmation
675 wf_engine.SetItemAttrText(
676 itemtype => item_type,
677 itemkey => item_key,
678 aname => 'ECX_CONFIRM_STATUSLVL',
679 avalue => l_confirm_statuslvl);
680
681 wf_engine.SetItemAttrText(
682 itemtype => item_type,
683 itemkey => item_key,
684 aname => 'ECX_CONFIRM_DESCRTN',
685 avalue => l_confirm_descrtn);
686
687 wf_engine.SetItemAttrText(
688 itemtype => item_type,
689 itemkey => item_key,
690 aname => 'ECX_CONFIRM_DET_DESCRIPTN',
691 avalue => l_data);
692
693 wf_engine.SetItemAttrText(
694 itemtype => item_type,
695 itemkey => item_key,
696 aname => 'ECX_CONFIRM_DET_REASONCODE',
697 avalue => l_confirm_statuslvl);
698
699 wf_engine.SetItemAttrText(
700 itemtype => item_type,
701 itemkey => item_key,
702 aname => 'ECX_EVENT_KEY',
703 avalue => l_interface_rec.transaction_type||'.'||l_trans_rec.item_no||'.'||l_ext_txn_id);
704
705 --RAISE;
706
707 END process_transaction;
708
709
710 ---------------------------------------------------------------------------------
711
712 PROCEDURE create_item (
713 item_type IN VARCHAR2,
714 item_key IN VARCHAR2,
715 actid IN NUMBER,
716 funcmode IN VARCHAR2,
717 resultout IN OUT NOCOPY VARCHAR2 )
718 IS
719
720 l_item_interface_rec gmi_items_xml_interface%ROWTYPE;
721 l_item_rec Gmigapi.item_rec_typ;
722
723 l_ic_item_mst_row ic_item_mst%ROWTYPE;
724 l_ic_item_cpg_row ic_item_cpg%ROWTYPE;
725
726 l_status VARCHAR2(1);
727 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
728 l_count NUMBER;
729 l_count_msg NUMBER;
730 l_data VARCHAR2(2000);
731 l_dummy_cnt NUMBER :=0;
732 l_record_count NUMBER :=0;
733
734 CURSOR c_item_rec(p_item_interface_id NUMBER, p_ext_item_id NUMBER) IS
735 SELECT *
736 FROM gmi_items_xml_interface
737 WHERE item_interface_id = p_item_interface_id
738 AND EXT_ITEM_ID = p_ext_item_id;
739
740 l_item_iface_id NUMBER(15);
741 l_ext_item_id NUMBER(15);
742
743 e_item_not_found EXCEPTION;
744 e_item_creation_failed EXCEPTION;
745
746 l_int_control_number NUMBER;
747 l_party_type VARCHAR2(256);
748
749 l_trigger_id PLS_INTEGER;
750 l_retcode PLS_INTEGER;
751 l_errmsg VARCHAR2(2000) ;
752
753 l_event_name VARCHAR2(100);
754 l_event_key VARCHAR2(100);
755 l_icn NUMBER;
756
757 l_confirm_statuslvl VARCHAR2(500);
758 l_confirm_descrtn VARCHAR2(500);
759
760
761 BEGIN
762 IF( funcmode = 'RUN' )
763 THEN
764 resultout := 'COMPLETE:Y';
765
766 -- Get the variables and call the api procedure
767 l_item_iface_id := wf_engine.GetItemAttrNumber(
768 itemtype => item_type,
769 itemkey => item_key,
770 aname => 'GMI_ITEM_INTERFACE_ID');
771
772 l_ext_item_id := wf_engine.GetItemAttrNumber(
773 itemtype => item_type,
774 itemkey => item_key,
775 aname => 'EXT_ITEM_ID');
776
777 OPEN c_item_rec(l_item_iface_id, l_ext_item_id);
778 FETCH c_item_rec INTO l_item_interface_rec;
779 IF( c_item_rec%NOTFOUND )
780 THEN
781 CLOSE c_item_rec;
782 RAISE e_item_not_found;
783 END IF;
784 CLOSE c_item_rec;
785
786 Wf_engine.SetItemAttrText(
787 itemtype => item_type,
788 itemkey => item_key,
789 aname => 'GMI_ITEM_NUMBER',
790 avalue => l_item_interface_rec.item_number);
791
792 l_item_rec.item_no := l_item_interface_rec.ITEM_NUMBER ;
793 l_item_rec.item_desc1 := l_item_interface_rec.ITEM_DESC1 ;
794 l_item_rec.item_desc2 := l_item_interface_rec.ITEM_DESC2 ;
795 l_item_rec.alt_itema := l_item_interface_rec.ALT_ITEMA ;
796 l_item_rec.alt_itemb := l_item_interface_rec.ALT_ITEMB ;
797 l_item_rec.item_um := l_item_interface_rec.ITEM_UOM ;
798 l_item_rec.dualum_ind := l_item_interface_rec.DUALUM_IND ;
799 l_item_rec.item_um2 := l_item_interface_rec.ITEM_UOM2 ;
800 l_item_rec.deviation_lo := l_item_interface_rec.DEVIATION_LO ;
801 l_item_rec.deviation_hi := l_item_interface_rec.DEVIATION_HI ;
802 l_item_rec.level_code := l_item_interface_rec.LEVEL_CODE ;
803 l_item_rec.lot_ctl := l_item_interface_rec.LOT_CTL ;
804 l_item_rec.lot_indivisible := l_item_interface_rec.LOT_INDIVISIBLE ;
805 l_item_rec.sublot_ctl := l_item_interface_rec.SUBLOT_CTL ;
806 l_item_rec.loct_ctl := l_item_interface_rec.LOCT_CTL ;
807 l_item_rec.noninv_ind := l_item_interface_rec.NONINV_IND ;
808 l_item_rec.match_type := l_item_interface_rec.MATCH_TYPE ;
809 l_item_rec.inactive_ind := l_item_interface_rec.INACTIVE_IND ;
810 l_item_rec.inv_type := l_item_interface_rec.INV_TYPE ;
811 l_item_rec.shelf_life := l_item_interface_rec.SHELF_LIFE ;
812 l_item_rec.retest_interval := l_item_interface_rec.RETEST_INTERVAL ;
813 l_item_rec.item_abccode := l_item_interface_rec.ITEM_ABCCODE ;
814 l_item_rec.gl_class := l_item_interface_rec.GL_CLASS ;
815 l_item_rec.inv_class := l_item_interface_rec.INV_CLASS ;
816 l_item_rec.sales_class := l_item_interface_rec.SALES_CLASS ;
817 l_item_rec.ship_class := l_item_interface_rec.SHIP_CLASS ;
818 l_item_rec.frt_class := l_item_interface_rec.FRT_CLASS ;
819 l_item_rec.price_class := l_item_interface_rec.PRICE_CLASS ;
820 l_item_rec.storage_class := l_item_interface_rec.STORAGE_CLASS ;
821 l_item_rec.purch_class := l_item_interface_rec.PURCH_CLASS ;
822 l_item_rec.tax_class := l_item_interface_rec.TAX_CLASS ;
823 l_item_rec.customs_class := l_item_interface_rec.CUSTOMS_CLASS ;
824 l_item_rec.alloc_class := l_item_interface_rec.ALLOC_CLASS ;
825 l_item_rec.planning_class := l_item_interface_rec.PLANNING_CLASS ;
826 l_item_rec.itemcost_class := l_item_interface_rec.ITEMCOST_CLASS ;
827 l_item_rec.cost_mthd_code := l_item_interface_rec.COST_MTHD_CODE ;
828 l_item_rec.upc_code := l_item_interface_rec.UPC_CODE ;
829 l_item_rec.grade_ctl := l_item_interface_rec.GRADE_CTL ;
830 l_item_rec.status_ctl := l_item_interface_rec.STATUS_CTL ;
831 l_item_rec.qc_grade := l_item_interface_rec.QC_GRADE ;
832 l_item_rec.lot_status := l_item_interface_rec.LOT_STATUS ;
833 l_item_rec.bulk_id := l_item_interface_rec.BULK_ID ;
834 l_item_rec.pkg_id := l_item_interface_rec.PKG_ID ;
835 l_item_rec.qcitem_no := l_item_interface_rec.QCITEM_NUMBER ;
836 l_item_rec.qchold_res_code := l_item_interface_rec.QCHOLD_RES_CODE ;
837 l_item_rec.expaction_code := l_item_interface_rec.EXPACTION_CODE ;
838 l_item_rec.fill_qty := l_item_interface_rec.FILL_QTY ;
839 l_item_rec.fill_um := l_item_interface_rec.FILL_UM ;
840 l_item_rec.expaction_interval := l_item_interface_rec.EXPACTION_INTERVAL ;
841 l_item_rec.phantom_type := l_item_interface_rec.PHANTOM_TYPE ;
842 l_item_rec.whse_item_no := l_item_interface_rec.WHSE_ITEM_NUMBER ;
843 l_item_rec.experimental_ind := l_item_interface_rec.EXPERIMENTAL_IND ;
844 --l_item_rec.exported_date := l_item_interface_rec.EXPORTED_DATE ;
845 l_item_rec.seq_dpnd_class := l_item_interface_rec.SEQ_DPND_CLASS ;
846 l_item_rec.commodity_code := l_item_interface_rec.COMMODITY_CODE ;
847 l_item_rec.ic_matr_days := l_item_interface_rec.IC_MATR_DAYS ;
848 l_item_rec.ic_hold_days := l_item_interface_rec.IC_HOLD_DAYS ;
849 l_item_rec.attribute1 := l_item_interface_rec.ATTRIBUTE1 ;
850 l_item_rec.attribute2 := l_item_interface_rec.ATTRIBUTE2 ;
851 l_item_rec.attribute3 := l_item_interface_rec.ATTRIBUTE3 ;
852 l_item_rec.attribute4 := l_item_interface_rec.ATTRIBUTE4 ;
853 l_item_rec.attribute5 := l_item_interface_rec.ATTRIBUTE5 ;
854 l_item_rec.attribute6 := l_item_interface_rec.ATTRIBUTE6 ;
855 l_item_rec.attribute7 := l_item_interface_rec.ATTRIBUTE7 ;
856 l_item_rec.attribute8 := l_item_interface_rec.ATTRIBUTE8 ;
857 l_item_rec.attribute9 := l_item_interface_rec.ATTRIBUTE9 ;
858 l_item_rec.attribute10 := l_item_interface_rec.ATTRIBUTE10 ;
859 l_item_rec.attribute11 := l_item_interface_rec.ATTRIBUTE11 ;
860 l_item_rec.attribute12 := l_item_interface_rec.ATTRIBUTE12 ;
861 l_item_rec.attribute13 := l_item_interface_rec.ATTRIBUTE13 ;
862 l_item_rec.attribute14 := l_item_interface_rec.ATTRIBUTE14 ;
863 l_item_rec.attribute15 := l_item_interface_rec.ATTRIBUTE15 ;
864 l_item_rec.attribute16 := l_item_interface_rec.ATTRIBUTE16 ;
865 l_item_rec.attribute17 := l_item_interface_rec.ATTRIBUTE17 ;
866 l_item_rec.attribute18 := l_item_interface_rec.ATTRIBUTE18 ;
867 l_item_rec.attribute19 := l_item_interface_rec.ATTRIBUTE19 ;
868 l_item_rec.attribute20 := l_item_interface_rec.ATTRIBUTE20 ;
869 l_item_rec.attribute21 := l_item_interface_rec.ATTRIBUTE21 ;
870 l_item_rec.attribute22 := l_item_interface_rec.ATTRIBUTE22 ;
871 l_item_rec.attribute23 := l_item_interface_rec.ATTRIBUTE23 ;
872 l_item_rec.attribute24 := l_item_interface_rec.ATTRIBUTE24 ;
873 l_item_rec.attribute25 := l_item_interface_rec.ATTRIBUTE25 ;
874 l_item_rec.attribute26 := l_item_interface_rec.ATTRIBUTE26 ;
875 l_item_rec.attribute27 := l_item_interface_rec.ATTRIBUTE27 ;
876 l_item_rec.attribute28 := l_item_interface_rec.ATTRIBUTE28 ;
877 l_item_rec.attribute29 := l_item_interface_rec.ATTRIBUTE29 ;
878 l_item_rec.attribute30 := l_item_interface_rec.ATTRIBUTE30 ;
879 l_item_rec.attribute_category := l_item_interface_rec.ATTRIBUTE_CATEGORY ;
880 --l_item_rec.user_name := l_item_interface_rec.USER_NAME ;
881 l_item_rec.ont_pricing_qty_source:= l_item_interface_rec.ONT_PRICING_QTY_SOURCE;
882
883 IF ( l_item_interface_rec.EXPORTED_DATE IS NULL )
884 THEN
885 l_item_rec.exported_date :=TO_DATE('02011970','DDMMYYYY');
886 ELSE
887 l_item_rec.exported_date :=TO_DATE(l_item_interface_rec.EXPORTED_DATE,
888 'YYYY/MM/DD HH24:MI:SS');
889 END IF;
890
891 IF( l_item_interface_rec.user_name IS NULL )
892 THEN
893 l_item_rec.user_name := 'OPM';
894 ELSE
895 l_item_rec.user_name := l_item_interface_rec.user_name;
896 END IF;
897
898 -- Set the context for the GMI APIs
899 IF( NOT Gmigutl.Setup(l_item_rec.user_name) )
900 THEN
901 RAISE e_item_creation_failed;
902 END IF;
903
904 -- Call the standard API and check the return status
905 Gmipapi.Create_Item
906 ( p_api_version => 3.0
907 , p_init_msg_list => FND_API.G_TRUE
908 , p_commit => FND_API.G_TRUE
909 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
910 , p_item_rec => l_item_rec
911 , x_ic_item_mst_row => l_ic_item_mst_row
912 , x_ic_item_cpg_row => l_ic_item_cpg_row
913 , x_return_status => l_status
914 , x_msg_count => l_count
915 , x_msg_data => l_data
916 );
917
918
919 -- for the outbound confirmation set the party type as internal.
920 l_party_type := 'I';
921
922 IF( l_status IN ('U','E') )
923 THEN
924
925 RAISE e_item_creation_failed;
926
927 ELSE
928
929 FOR l_loop_cnt IN 1..l_count
930 LOOP
931
932 FND_MSG_PUB.Get(
933 p_msg_index => l_loop_cnt,
934 p_data => l_data,
935 p_encoded => FND_API.G_FALSE,
936 p_msg_index_out => l_dummy_cnt);
937
938 -- write to log
939 /*log_message( p_proc_name => 'create_item',
940 p_if_id => l_item_iface_id,
941 p_msg => l_data);*/
942
943 END LOOP;
944
945 l_icn := wf_engine.GetItemAttrNumber(
946 itemtype => item_type,
947 itemkey => item_key,
948 aname => 'PARAMETER7');
949
950 l_event_key := l_item_rec.item_no||'.'||l_ext_item_id;
951
952
953 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_SUCCESS');
954 l_confirm_statuslvl := FND_MESSAGE.get;
955
956 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_ITEM_S');
957 l_confirm_descrtn := FND_MESSAGE.get;
958
959 send_outbound_document( l_confirm_statuslvl,
960 l_confirm_descrtn,
961 l_data,
962 l_confirm_statuslvl,
963 l_icn,
964 l_event_key);
965
966 DELETE FROM gmi_items_xml_interface
967 WHERE item_interface_id = l_item_iface_id
968 AND EXT_ITEM_ID = l_ext_item_id;
969
970 resultout := 'COMPLETE:Y';
971
972 END IF;
973
974 /* Update error status */
975 l_return_status := l_status;
976
977 -- End of funcmode = RUN
978
979 ELSIF funcmode = 'CANCEL'
980 THEN
981 resultout := 'COMPLETE';
982
983 ELSIF funcmode = 'RESPOND'
984 THEN
985 resultout := 'COMPLETE';
986
987 ELSIF funcmode = 'FORWARD'
988 THEN
989 resultout := 'COMPLETE';
990
991 ELSIF funcmode = 'TRANSFER'
992 THEN
993 resultout := 'COMPLETE';
994
995 ELSIF funcmode = 'TIMEOUT'
996 THEN
997 resultout := 'COMPLETE';
998
999 ELSE
1000 resultout := NULL;
1001
1002 END IF;
1003
1004 EXCEPTION
1005 WHEN e_item_not_found THEN
1006 log_message(
1007 p_proc_name => 'create_item',
1008 p_if_id => l_item_iface_id,
1009 p_msg => 'Could not find the interface record for if id =>'||
1010 l_item_iface_id || ' ext item id =>' || l_ext_item_id);
1011
1012 resultout := 'COMPLETE:N';
1013
1014 RAISE;
1015
1016 WHEN e_item_creation_failed THEN
1017 resultout := 'COMPLETE:N';
1018 -- API Failed. Error message must be on stack.
1019 l_count_msg := fnd_msg_pub.Count_Msg;
1020
1021 FOR l_loop_cnt IN 1..l_count_msg
1022 LOOP
1023 FND_MSG_PUB.GET(P_msg_index => l_loop_cnt,
1024 P_data => l_data,
1025 P_encoded => FND_API.G_FALSE,
1026 P_msg_index_out => l_dummy_cnt);
1027
1028 log_message(p_proc_name => 'create_item',
1029 p_if_id => l_item_iface_id,
1030 p_msg => 'Error :' || l_data);
1031 END LOOP;
1032
1033
1034 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_FAIL');
1035 l_confirm_statuslvl := FND_MESSAGE.get;
1036
1037 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_ITEM_F');
1038 l_confirm_descrtn := FND_MESSAGE.get;
1039
1040 -- Set the appropriate attributes for confirmation
1041 wf_engine.SetItemAttrText(
1042 itemtype => item_type,
1043 itemkey => item_key,
1044 aname => 'ECX_CONFIRM_STATUSLVL',
1045 avalue => l_confirm_statuslvl);
1046
1047 wf_engine.SetItemAttrText(
1048 itemtype => item_type,
1049 itemkey => item_key,
1050 aname => 'ECX_CONFIRM_DESCRTN',
1051 avalue => l_confirm_descrtn);
1052
1053 wf_engine.SetItemAttrText(
1054 itemtype => item_type,
1055 itemkey => item_key,
1056 aname => 'ECX_CONFIRM_DET_DESCRIPTN',
1057 avalue => l_data);
1058
1059 wf_engine.SetItemAttrText(
1060 itemtype => item_type,
1061 itemkey => item_key,
1062 aname => 'ECX_CONFIRM_DET_REASONCODE',
1063 avalue => l_confirm_statuslvl);
1064
1065 wf_engine.SetItemAttrText(
1066 itemtype => item_type,
1067 itemkey => item_key,
1068 aname => 'ECX_EVENT_KEY',
1069 avalue => l_item_rec.item_no||'.'||l_ext_item_id);
1070
1071 --RAISE;
1072
1073 END create_item;
1074
1075 --------------------------------------------------------------------------------
1076
1077
1078 PROCEDURE create_lot (
1079 item_type IN VARCHAR2,
1080 item_key IN VARCHAR2,
1081 actid IN NUMBER,
1082 funcmode IN VARCHAR2,
1083 resultout IN OUT NOCOPY VARCHAR2 )
1084 IS
1085
1086 l_interface_rec gmi_lots_xml_interface%ROWTYPE;
1087 l_trans_rec Gmigapi.lot_rec_typ;
1088
1089 l_ic_lots_mst_row ic_lots_mst%ROWTYPE;
1090 l_ic_lots_cpg_row ic_lots_cpg%ROWTYPE;
1091
1092 l_status VARCHAR2(1);
1093 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
1094 l_count NUMBER;
1095 l_count_msg NUMBER;
1096 l_data VARCHAR2(2000);
1097 l_dummy_cnt NUMBER :=0;
1098 l_record_count NUMBER :=0;
1099
1100 CURSOR c_if_rec(p_interface_id NUMBER, p_ext_lot_id NUMBER) IS
1101 SELECT *
1102 FROM gmi_lots_xml_interface
1103 WHERE lot_interface_id = p_interface_id
1104 AND ext_lot_id = p_ext_lot_id;
1105
1106 l_lot_iface_id NUMBER(15);
1107 l_ext_lot_id NUMBER(15);
1108
1109 e_lot_not_found EXCEPTION;
1110 e_lot_failed EXCEPTION;
1111
1112 l_event_name VARCHAR2(100);
1113 l_event_key VARCHAR2(100);
1114 l_icn NUMBER;
1115
1116 l_confirm_statuslvl VARCHAR2(500);
1117 l_confirm_descrtn VARCHAR2(500);
1118
1119 l_party_type VARCHAR2(256);
1120 l_ext_txn_id NUMBER(15);
1121
1122 BEGIN
1123 IF( funcmode = 'RUN' )
1124 THEN
1125 resultout := 'COMPLETE:Y';
1126
1127 -- Get the variables and call the api procedure
1128 l_lot_iface_id := wf_engine.GetItemAttrNumber(
1129 itemtype => item_type,
1130 itemkey => item_key,
1131 aname => 'GMI_LOT_INTERFACE_ID');
1132
1133 l_ext_lot_id := wf_engine.GetItemAttrNumber(
1134 itemtype => item_type,
1135 itemkey => item_key,
1136 aname => 'EXT_LOT_ID');
1137
1138 OPEN c_if_rec(l_lot_iface_id, l_ext_lot_id);
1139 FETCH c_if_rec INTO l_interface_rec;
1140 IF( c_if_rec%NOTFOUND )
1141 THEN
1142 CLOSE c_if_rec;
1143 RAISE e_lot_not_found;
1144 END IF;
1145 CLOSE c_if_rec;
1146
1147 Wf_engine.SetItemAttrText(
1148 itemtype => item_type,
1149 itemkey => item_key,
1150 aname => 'GMI_ITEM_NUMBER',
1151 avalue => l_interface_rec.item_number);
1152
1153
1154 l_trans_rec.item_no := l_interface_rec.item_number;
1155 l_trans_rec.lot_no := l_interface_rec.lot_number;
1156 l_trans_rec.sublot_no := l_interface_rec.sublot_number;
1157 l_trans_rec.lot_desc := l_interface_rec.lot_desc;
1158 l_trans_rec.qc_grade := l_interface_rec.qc_grade;
1159 l_trans_rec.expaction_code := l_interface_rec.expaction_code;
1160 IF (l_interface_rec.expaction_date IS NOT NULL) THEN
1161 l_trans_rec.expaction_date := TO_DATE(l_interface_rec.expaction_date,
1162 'YYYY/MM/DD HH24:MI:SS');
1163 END IF;
1164 IF (l_interface_rec.lot_created IS NOT NULL) THEN
1165 l_trans_rec.lot_created := TO_DATE(l_interface_rec.lot_created,
1166 'YYYY/MM/DD HH24:MI:SS');
1167 END IF;
1168 IF (l_interface_rec.expire_date IS NOT NULL) THEN
1169 l_trans_rec.expire_date := TO_DATE(l_interface_rec.expire_date,
1170 'YYYY/MM/DD HH24:MI:SS');
1171 END IF;
1172 IF (l_interface_rec.retest_date IS NOT NULL) THEN
1173 l_trans_rec.retest_date := TO_DATE(l_interface_rec.retest_date,
1174 'YYYY/MM/DD HH24:MI:SS');
1175 END IF;
1176 l_trans_rec.strength := l_interface_rec.strength;
1177 l_trans_rec.inactive_ind := l_interface_rec.inactive_ind;
1178 l_trans_rec.origination_type := l_interface_rec.origination_type;
1179 l_trans_rec.shipvendor_no := l_interface_rec.shipvendor_no;
1180 l_trans_rec.vendor_lot_no := l_interface_rec.vendor_lot_no;
1181 IF (l_interface_rec.ic_matr_date IS NOT NULL) THEN
1182 l_trans_rec.ic_matr_date := TO_DATE(l_interface_rec.ic_matr_date,
1183 'YYYY/MM/DD HH24:MI:SS');
1184 END IF;
1185 IF (l_interface_rec.ic_hold_date IS NOT NULL) THEN
1186 l_trans_rec.ic_hold_date := TO_DATE(l_interface_rec.ic_hold_date,
1187 'YYYY/MM/DD HH24:MI:SS');
1188 END IF;
1189 l_trans_rec.attribute1 := l_interface_rec.attribute1;
1190 l_trans_rec.attribute2 := l_interface_rec.attribute2;
1191 l_trans_rec.attribute3 := l_interface_rec.attribute3;
1192 l_trans_rec.attribute4 := l_interface_rec.attribute4;
1193 l_trans_rec.attribute5 := l_interface_rec.attribute5;
1194 l_trans_rec.attribute6 := l_interface_rec.attribute6;
1195 l_trans_rec.attribute7 := l_interface_rec.attribute7;
1196 l_trans_rec.attribute8 := l_interface_rec.attribute8;
1197 l_trans_rec.attribute9 := l_interface_rec.attribute9;
1198 l_trans_rec.attribute10 := l_interface_rec.attribute10;
1199 l_trans_rec.attribute11 := l_interface_rec.attribute11;
1200 l_trans_rec.attribute12 := l_interface_rec.attribute12;
1201 l_trans_rec.attribute13 := l_interface_rec.attribute13;
1202 l_trans_rec.attribute14 := l_interface_rec.attribute14;
1203 l_trans_rec.attribute15 := l_interface_rec.attribute15;
1204 l_trans_rec.attribute16 := l_interface_rec.attribute16;
1205 l_trans_rec.attribute17 := l_interface_rec.attribute17;
1206 l_trans_rec.attribute18 := l_interface_rec.attribute18;
1207 l_trans_rec.attribute19 := l_interface_rec.attribute19;
1208 l_trans_rec.attribute20 := l_interface_rec.attribute20;
1209 l_trans_rec.attribute21 := l_interface_rec.attribute21;
1210 l_trans_rec.attribute22 := l_interface_rec.attribute22;
1211 l_trans_rec.attribute23 := l_interface_rec.attribute23;
1212 l_trans_rec.attribute24 := l_interface_rec.attribute24;
1213 l_trans_rec.attribute25 := l_interface_rec.attribute25;
1214 l_trans_rec.attribute26 := l_interface_rec.attribute26;
1215 l_trans_rec.attribute27 := l_interface_rec.attribute27;
1216 l_trans_rec.attribute28 := l_interface_rec.attribute28;
1217 l_trans_rec.attribute29 := l_interface_rec.attribute29;
1218 l_trans_rec.attribute30 := l_interface_rec.attribute30;
1219 l_trans_rec.attribute_category := l_interface_rec.attribute_category;
1220
1221 IF (l_interface_rec.user_name IS NULL ) THEN
1222 l_trans_rec.user_name := 'OPM';
1223 ELSE
1224 l_trans_rec.user_name := l_interface_rec.user_name;
1225 END IF;
1226
1227
1228 -- Set the context for the GMI APIs
1229 IF( NOT Gmigutl.Setup(l_trans_rec.user_name) )
1230 THEN
1231 RAISE e_lot_failed;
1232 END IF;
1233
1234 -- Call the standard API and check the return status
1235 Gmipapi.Create_Lot
1236 ( p_api_version => 3.0
1237 , p_init_msg_list => FND_API.G_TRUE
1238 , p_commit => FND_API.G_FALSE
1239 , p_validation_level => FND_API.G_valid_level_full
1240 , p_lot_rec => l_trans_rec
1241 , x_ic_lots_mst_row => l_ic_lots_mst_row
1242 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
1243 , x_return_status => l_status
1244 , x_msg_count => l_count
1245 , x_msg_data => l_data
1246 );
1247
1248 l_party_type := 'I';
1249
1250 IF( l_status IN ('U','E') )
1251 THEN
1252 RAISE e_lot_failed;
1253 ELSE
1254
1255 FOR l_loop_cnt IN 1..l_count
1256 LOOP
1257
1258 FND_MSG_PUB.Get(
1259 p_msg_index => l_loop_cnt,
1260 p_data => l_data,
1261 p_encoded => FND_API.G_FALSE,
1262 p_msg_index_out => l_dummy_cnt);
1263
1264 -- write to log
1265 /*log_message(p_proc_name => 'create_lot',
1266 p_if_id => l_lot_iface_id,
1267 p_msg => l_data);*/
1268
1269 END LOOP;
1270
1271 l_icn := wf_engine.GetItemAttrNumber(
1272 itemtype => item_type,
1273 itemkey => item_key,
1274 aname => 'PARAMETER7');
1275
1276 l_event_key := l_interface_rec.item_number||'.'||l_trans_rec.lot_no||'.'||l_ext_lot_id;
1277
1278 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_SUCCESS');
1279 l_confirm_statuslvl := FND_MESSAGE.get;
1280
1281 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_LOT_S');
1282 l_confirm_descrtn := FND_MESSAGE.get;
1283
1284 send_outbound_document( l_confirm_statuslvl,
1285 l_confirm_descrtn,
1286 l_data,
1287 l_confirm_statuslvl,
1288 l_icn,
1289 l_event_key);
1290
1291 DELETE FROM gmi_lots_xml_interface
1292 WHERE lot_interface_id = l_lot_iface_id
1293 AND ext_lot_id = l_ext_lot_id;
1294
1295 resultout := 'COMPLETE:Y';
1296 END IF;
1297
1298
1299
1300 /* Update error status */
1301 l_return_status := l_status;
1302
1303 -- End of funcmode = RUN
1304
1305 ELSIF funcmode = 'CANCEL'
1306 THEN
1307 resultout := 'COMPLETE';
1308
1309 ELSIF funcmode = 'RESPOND'
1310 THEN
1311 resultout := 'COMPLETE';
1312
1313 ELSIF funcmode = 'FORWARD'
1314 THEN
1315 resultout := 'COMPLETE';
1316
1317 ELSIF funcmode = 'TRANSFER'
1318 THEN
1319 resultout := 'COMPLETE';
1320
1321 ELSIF funcmode = 'TIMEOUT'
1322 THEN
1323 resultout := 'COMPLETE';
1324
1325 ELSE
1326 resultout := NULL;
1327 END IF;
1328
1329 EXCEPTION
1330 WHEN e_lot_not_found THEN
1331 log_message(
1332 p_proc_name => 'create_lot',
1333 p_if_id => l_lot_iface_id,
1334 p_msg => 'Could not find the interface record for if id =>'||
1335 l_lot_iface_id || ' ext id =>' || l_ext_lot_id);
1336
1337 resultout := 'COMPLETE:N';
1338
1339 RAISE;
1340
1341 WHEN e_lot_failed THEN
1342 resultout := 'COMPLETE:N';
1343 -- API Failed. Error message must be on stack.
1344 l_count_msg := fnd_msg_pub.Count_Msg;
1345
1346 FOR l_loop_cnt IN 1..l_count_msg
1347 LOOP
1348 FND_MSG_PUB.GET(P_msg_index => l_loop_cnt,
1349 P_data => l_data,
1350 P_encoded => FND_API.G_FALSE,
1351 P_msg_index_out => l_dummy_cnt);
1352
1353 log_message(p_proc_name => 'create_lot',
1354 p_if_id => l_lot_iface_id,
1355 p_msg => 'Error :' || l_data);
1356 END LOOP;
1357
1358
1359 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_FAIL');
1360 l_confirm_statuslvl := FND_MESSAGE.get;
1361
1362 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_LOT_F');
1363 l_confirm_descrtn := FND_MESSAGE.get;
1364
1365 -- Set the appropriate attributes for confirmation
1366 wf_engine.SetItemAttrText(
1367 itemtype => item_type,
1368 itemkey => item_key,
1369 aname => 'ECX_CONFIRM_STATUSLVL',
1370 avalue => l_confirm_statuslvl);
1371
1372 wf_engine.SetItemAttrText(
1373 itemtype => item_type,
1374 itemkey => item_key,
1375 aname => 'ECX_CONFIRM_DESCRTN',
1376 avalue => l_confirm_descrtn);
1377
1378 wf_engine.SetItemAttrText(
1379 itemtype => item_type,
1380 itemkey => item_key,
1381 aname => 'ECX_CONFIRM_DET_DESCRIPTN',
1382 avalue => l_data);
1383
1384 wf_engine.SetItemAttrText(
1385 itemtype => item_type,
1386 itemkey => item_key,
1387 aname => 'ECX_CONFIRM_DET_REASONCODE',
1388 avalue => l_confirm_statuslvl);
1389
1390 wf_engine.SetItemAttrText(
1391 itemtype => item_type,
1392 itemkey => item_key,
1393 aname => 'ECX_EVENT_KEY',
1394 avalue => l_interface_rec.item_number||'.'||l_trans_rec.lot_no||'.'||l_ext_txn_id);
1395
1396 --RAISE;
1397
1398
1399 END create_lot;
1400
1401 ----------------------------------------------------------------------------
1402 PROCEDURE create_lot_conversion (
1403 item_type IN VARCHAR2,
1404 item_key IN VARCHAR2,
1405 actid IN NUMBER,
1406 funcmode IN VARCHAR2,
1407 resultout IN OUT NOCOPY VARCHAR2 )
1408 IS
1409
1410 l_lot_interface_rec gmi_lots_conv_xml_interface%ROWTYPE;
1411 l_trans_rec Gmigapi.conv_rec_typ;
1412
1413 l_ic_item_cnv_row IC_ITEM_CNV%ROWTYPE;
1414
1415
1416
1417 l_status VARCHAR2(1);
1418 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
1419 l_count NUMBER;
1420 l_count_msg NUMBER;
1421 l_data VARCHAR2(2000);
1422 l_dummy_cnt NUMBER :=0;
1423 l_record_count NUMBER :=0;
1424
1425 CURSOR c_if_rec(p_interface_id NUMBER, p_ext_lot_id NUMBER) IS
1426 SELECT *
1427 FROM gmi_lots_conv_xml_interface
1428 WHERE conv_interface_id = p_interface_id
1429 AND ext_conv_id = p_ext_lot_id;
1430
1431 l_lot_iface_id NUMBER(15);
1432 l_ext_lot_id NUMBER(15);
1433
1434 e_lot_not_found EXCEPTION;
1435 e_lot_conv_failed EXCEPTION;
1436
1437 l_event_name VARCHAR2(100);
1438 l_event_key VARCHAR2(100);
1439 l_icn NUMBER;
1440
1441 l_confirm_statuslvl VARCHAR2(500);
1442 l_confirm_descrtn VARCHAR2(500);
1443
1444 l_ext_txn_id NUMBER(15);
1445 l_party_type VARCHAR2(256);
1446
1447 BEGIN
1448 IF( funcmode = 'RUN' )
1449 THEN
1450 resultout := 'COMPLETE:Y';
1451
1452 -- Get the variables and call the api procedure
1453 l_lot_iface_id := wf_engine.GetItemAttrNumber(
1454 itemtype => item_type,
1455 itemkey => item_key,
1456 aname => 'CONV_INTERFACE_ID');
1457
1458 l_ext_lot_id := wf_engine.GetItemAttrNumber(
1459 itemtype => item_type,
1460 itemkey => item_key,
1461 aname => 'EXT_CONV_ID');
1462
1463 OPEN c_if_rec(l_lot_iface_id, l_ext_lot_id);
1464 FETCH c_if_rec INTO l_lot_interface_rec;
1465 IF( c_if_rec%NOTFOUND )
1466 THEN
1467 CLOSE c_if_rec;
1468 RAISE e_lot_not_found;
1469 END IF;
1470 CLOSE c_if_rec;
1471
1472 Wf_engine.SetItemAttrText(
1473 itemtype => item_type,
1474 itemkey => item_key,
1475 aname => 'GMI_ITEM_NUMBER',
1476 avalue => l_lot_interface_rec.item_number);
1477
1478
1479 l_trans_rec.item_no := l_lot_interface_rec.item_number;
1480 l_trans_rec.lot_no := l_lot_interface_rec.lot_number;
1481 l_trans_rec.sublot_no := l_lot_interface_rec.sublot_number;
1482 l_trans_rec.from_uom := l_lot_interface_rec.from_uom;
1483 l_trans_rec.to_uom := l_lot_interface_rec.to_uom;
1484 l_trans_rec.type_factor := l_lot_interface_rec.type_factor;
1485
1486
1487 IF (l_lot_interface_rec.user_name IS NULL ) THEN
1488 l_trans_rec.user_name := 'OPM';
1489 ELSE
1490 l_trans_rec.user_name := l_lot_interface_rec.user_name;
1491 END IF;
1492
1493
1494 -- Set the context for the GMI APIs
1495 IF( NOT Gmigutl.Setup(l_trans_rec.user_name) )
1496 THEN
1497 RAISE e_lot_conv_failed;
1498 END IF;
1499
1500 -- Call the standard API and check the return status
1501 Gmipapi.Create_Item_Lot_Conv
1502 ( p_api_version => 3.0
1503 , p_init_msg_list => FND_API.G_TRUE
1504 , p_commit => FND_API.G_FALSE
1505 , p_validation_level => FND_API.G_valid_level_full
1506 , p_conv_rec => l_trans_rec
1507 , x_ic_item_cnv_row => l_ic_item_cnv_row
1508 , x_return_status => l_status
1509 , x_msg_count => l_count
1510 , x_msg_data => l_data
1511 );
1512
1513 l_party_type := 'I';
1514
1515 IF( l_status IN ('U','E') )
1516 THEN
1517 RAISE e_lot_conv_failed;
1518
1519 ELSE
1520 FOR l_loop_cnt IN 1..l_count
1521 LOOP
1522
1523 FND_MSG_PUB.Get(
1524 p_msg_index => l_loop_cnt,
1525 p_data => l_data,
1526 p_encoded => FND_API.G_FALSE,
1527 p_msg_index_out => l_dummy_cnt);
1528
1529 -- write to log
1530 /*log_message(p_proc_name => 'create_lot_conversion',
1531 p_if_id => l_lot_iface_id,
1532 p_msg => l_data);*/
1533
1534 END LOOP;
1535
1536
1537 l_icn := wf_engine.GetItemAttrNumber(
1538 itemtype => item_type,
1539 itemkey => item_key,
1540 aname => 'PARAMETER7');
1541
1542 l_event_key := l_lot_interface_rec.item_number||'.'||l_trans_rec.lot_no||'.'||l_ext_lot_id;
1543
1544
1545 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_SUCCESS');
1546 l_confirm_statuslvl := FND_MESSAGE.get;
1547
1548 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_LTCV_S');
1549
1550 l_confirm_descrtn := FND_MESSAGE.get;
1551
1552 send_outbound_document( l_confirm_statuslvl,
1553 l_confirm_descrtn,
1554 l_data,
1555 l_confirm_statuslvl,
1556 l_icn,
1557 l_event_key);
1558
1559 DELETE FROM gmi_lots_conv_xml_interface
1560 WHERE conv_interface_id = l_lot_iface_id
1561 AND ext_conv_id = l_ext_lot_id;
1562
1563
1564 resultout := 'COMPLETE:Y';
1565 END IF;
1566
1567 /* Update error status */
1568 l_return_status := l_status;
1569
1570 -- End of funcmode = RUN
1571
1572 ELSIF funcmode = 'CANCEL'
1573 THEN
1574 resultout := 'COMPLETE';
1575
1576 ELSIF funcmode = 'RESPOND'
1577 THEN
1578 resultout := 'COMPLETE';
1579
1580 ELSIF funcmode = 'FORWARD'
1581 THEN
1582 resultout := 'COMPLETE';
1583
1584 ELSIF funcmode = 'TRANSFER'
1585 THEN
1586 resultout := 'COMPLETE';
1587
1588 ELSIF funcmode = 'TIMEOUT'
1589 THEN
1590 resultout := 'COMPLETE';
1591
1592 ELSE
1593 resultout := NULL;
1594 END IF;
1595
1596 EXCEPTION
1597 WHEN e_lot_not_found THEN
1598 log_message(
1599 p_proc_name => 'create_lot_conversion',
1600 p_if_id => l_lot_iface_id,
1601 p_msg => 'Could not find the interface record for if id =>'||
1602 l_lot_iface_id || ' ext id =>' || l_ext_lot_id);
1603
1604 resultout := 'COMPLETE:N';
1605
1606 RAISE;
1607
1608 WHEN e_lot_conv_failed THEN
1609 resultout := 'COMPLETE:N';
1610 -- API Failed. Error message must be on stack.
1611 l_count_msg := fnd_msg_pub.Count_Msg;
1612
1613 FOR l_loop_cnt IN 1..l_count_msg
1614 LOOP
1615 FND_MSG_PUB.GET(P_msg_index => l_loop_cnt,
1616 P_data => l_data,
1617 P_encoded => FND_API.G_FALSE,
1618 P_msg_index_out => l_dummy_cnt);
1619
1620 log_message(p_proc_name => 'create_lot_conversion',
1621 p_if_id => l_lot_iface_id,
1622 p_msg => 'Error :' || l_data);
1623 END LOOP;
1624
1625
1626 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_STATUS_FAIL');
1627 l_confirm_statuslvl := FND_MESSAGE.get;
1628
1629 FND_MESSAGE.set_name('GMI', 'GMI_XML_CONFIRM_DESCRTN_LTCV_F');
1630 l_confirm_descrtn := FND_MESSAGE.get;
1631
1632 -- Set the appropriate attributes for confirmation
1633 wf_engine.SetItemAttrText(
1634 itemtype => item_type,
1635 itemkey => item_key,
1636 aname => 'ECX_CONFIRM_STATUSLVL',
1637 avalue => l_confirm_statuslvl);
1638
1639 wf_engine.SetItemAttrText(
1640 itemtype => item_type,
1641 itemkey => item_key,
1642 aname => 'ECX_CONFIRM_DESCRTN',
1643 avalue => l_confirm_descrtn);
1644
1645 wf_engine.SetItemAttrText(
1646 itemtype => item_type,
1647 itemkey => item_key,
1648 aname => 'ECX_CONFIRM_DET_DESCRIPTN',
1649 avalue => l_data);
1650
1651 wf_engine.SetItemAttrText(
1652 itemtype => item_type,
1653 itemkey => item_key,
1654 aname => 'ECX_CONFIRM_DET_REASONCODE',
1655 avalue => l_confirm_statuslvl);
1656
1657 wf_engine.SetItemAttrText(
1658 itemtype => item_type,
1659 itemkey => item_key,
1660 aname => 'ECX_EVENT_KEY',
1661 avalue => l_lot_interface_rec.item_number||'.'||l_trans_rec.lot_no||'.'||l_ext_txn_id);
1662
1663 --RAISE;
1664
1665
1666 END create_lot_conversion;
1667
1668 -------------------------------------------------------------------
1669 PROCEDURE log_message( p_proc_name IN VARCHAR2,
1670 p_if_id IN NUMBER,
1671 p_msg IN VARCHAR2)
1672 IS
1673
1674 BEGIN
1675 wf_core.context(pkg_name => G_PKG_NAME,
1676 proc_name => p_proc_name,
1677 arg1 => p_msg);
1678
1679 -- Also update the interface table with status/messages
1680 update_status( p_proc_name => p_proc_name,
1681 p_if_id => p_if_id,
1682 p_err_msg => p_msg);
1683
1684 END log_message;
1685
1686 ------------------------------------------------------------------
1687 PROCEDURE update_status(
1688 p_proc_name IN VARCHAR2,
1689 p_if_id IN NUMBER,
1690 p_err_msg IN VARCHAR2)
1691 IS
1692 PRAGMA AUTONOMOUS_TRANSACTION;
1693
1694 BEGIN
1695 IF p_proc_name = 'process_transaction' THEN
1696 UPDATE gmi_quantity_xml_interface
1697 SET error_text = SUBSTRB(error_text || p_err_msg, 1, 2000),
1698 processed_ind = 3
1699 WHERE quantity_interface_id = p_if_id;
1700 ELSIF p_proc_name = 'create_item' THEN
1701 UPDATE gmi_items_xml_interface
1702 SET error_text = SUBSTRB(error_text || p_err_msg, 1, 2000),
1703 processed_ind = 3
1704 WHERE item_interface_id = p_if_id;
1705 ELSIF p_proc_name = 'create_lot' THEN
1706 UPDATE gmi_lots_xml_interface
1707 SET error_text = SUBSTRB(error_text || p_err_msg, 1, 2000),
1708 processed_ind = 3
1709 WHERE lot_interface_id = p_if_id;
1710 ELSIF p_proc_name = 'create_lot_conv' THEN
1711 UPDATE gmi_lots_conv_xml_interface
1712 SET error_text = SUBSTRB(error_text || p_err_msg, 1, 2000),
1713 processed_ind = 3
1714 WHERE conv_interface_id = p_if_id;
1715 END IF;
1716
1717 COMMIT;
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 NULL;
1722
1723 END update_status;
1724
1725 -----------------------------------------------------------------------------------------
1726
1727 PROCEDURE confirm_api_selector ( item_type IN VARCHAR2,
1728 item_key IN VARCHAR2,
1729 actid IN NUMBER,
1730 command IN VARCHAR2,
1731 resultout IN OUT NOCOPY VARCHAR2 ) IS
1732
1733 BEGIN
1734
1735 IF( command = 'RUN' )
1736 THEN
1737 resultout := 'CONFIRM_API'; -- process name
1738
1739 ELSIF( command = 'SET_CTX' )
1740 THEN
1741 NULL;
1742 ELSIF( command = 'TEST_CTX' )
1743 THEN
1744 resultout := 'TRUE';
1745
1746 END IF;
1747
1748 END confirm_api_selector;
1749
1750 -----------------------------------------------------------------------------------------
1751
1752 PROCEDURE send_outbound_document( p_confirm_statuslvl IN VARCHAR2,
1753 p_confirm_descrtn IN VARCHAR2,
1754 p_confirm_det_descriptn IN VARCHAR2,
1755 p_confirm_det_reasoncode IN VARCHAR2,
1756 p_icn IN NUMBER,
1757 p_event_key IN VARCHAR2)
1758 IS
1759
1760 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1761 l_event_name VARCHAR2(120);
1762
1763 BEGIN
1764
1765 wf_event.AddParameterToList( p_name=>'ECX_TRANSACTION_TYPE',
1766 p_value=>'ECX',
1767 p_parameterlist=>l_parameter_list);
1768
1769 wf_event.AddParameterToList( p_name=>'ECX_TRANSACTION_SUBTYPE',
1770 p_value=>'CBODO',
1771 p_parameterlist=>l_parameter_list);
1772
1773 wf_event.AddParameterToList( p_name=>'DOCUMENT_ID',
1774 p_value=>p_icn,
1775 p_parameterlist=>l_parameter_list);
1776
1777 wf_event.AddParameterToList( p_name=>'PARTY_TYPE',
1778 p_value=>'I',
1779 p_parameterlist=>l_parameter_list);
1780
1781 wf_event.AddParameterToList( p_name=>'SEND_MODE',
1782 p_value=>'Immediate',
1783 p_parameterlist=>l_parameter_list);
1784
1785 wf_event.AddParameterToList( p_name=>'CONFIRM_STATUSLVL',
1786 p_value=>p_confirm_statuslvl,
1787 p_parameterlist=>l_parameter_list);
1788
1789 wf_event.AddParameterToList( p_name=>'CONFIRM_DESCRTN',
1790 p_value=>p_confirm_descrtn,
1791 p_parameterlist=>l_parameter_list);
1792
1793 wf_event.AddParameterToList( p_name=>'CONFIRM_DET_DESCRIPTN',
1794 p_value=>p_confirm_det_descriptn ,
1795 p_parameterlist=>l_parameter_list);
1796
1797 wf_event.AddParameterToList( p_name=>'CONFIRM_DET_REASONCODE',
1798 p_value=>p_confirm_det_reasoncode,
1799 p_parameterlist=>l_parameter_list);
1800
1801 wf_event.AddParameterToList( p_name=>'ECX_MSGID_ATTR',
1802 p_value=>'ECX_MESSAGE_ID',
1803 p_parameterlist=>l_parameter_list);
1804
1805 l_event_name := 'oracle.apps.gmi.api.xml.confirm';
1806
1807 wf_event.raise( p_event_name => l_event_name,
1808 p_event_key => p_event_key,
1809 p_parameters => l_parameter_list);
1810
1811 l_parameter_list.DELETE;
1812
1813 END send_outbound_document;
1814
1815 ----------------------------------------------------------------------------
1816
1817 PROCEDURE send_error_cbod ( item_type IN VARCHAR2,
1818 item_key IN VARCHAR2,
1819 actid IN NUMBER,
1820 command IN VARCHAR2,
1821 resultout IN OUT NOCOPY VARCHAR2 ) IS
1822
1823 l_confirm_statuslvl VARCHAR2(240);
1824 l_confirm_descrtn VARCHAR2(240);
1825 l_confirm_det_descriptn VARCHAR2(240);
1826 l_confirm_det_reasoncode VARCHAR2(240);
1827 l_icn NUMBER;
1828 l_event_name VARCHAR2(120);
1829 l_event_key VARCHAR2(240);
1830 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1831
1832 BEGIN
1833
1834 l_confirm_statuslvl := wf_engine.GetItemAttrText(
1835 itemtype => item_type,
1836 itemkey => item_key,
1837 aname => 'ECX_CONFIRM_STATUSLVL');
1838
1839 l_confirm_descrtn := wf_engine.GetItemAttrText(
1840 itemtype => item_type,
1841 itemkey => item_key,
1842 aname => 'ECX_CONFIRM_DESCRTN');
1843
1844 l_confirm_det_descriptn := wf_engine.GetItemAttrText(
1845 itemtype => item_type,
1846 itemkey => item_key,
1847 aname => 'ECX_CONFIRM_DET_DESCRIPTN');
1848
1849 l_confirm_det_reasoncode := wf_engine.GetItemAttrText(
1850 itemtype => item_type,
1851 itemkey => item_key,
1852 aname => 'ECX_CONFIRM_DET_REASONCODE');
1853
1854 l_event_key := wf_engine.GetItemAttrText(
1855 itemtype => item_type,
1856 itemkey => item_key,
1857 aname => 'ECX_EVENT_KEY');
1858
1859 wf_event.AddParameterToList( p_name=>'ECX_TRANSACTION_TYPE',
1860 p_value=>'ECX',
1861 p_parameterlist=>l_parameter_list);
1862
1863 wf_event.AddParameterToList( p_name=>'ECX_TRANSACTION_SUBTYPE',
1864 p_value=>'CBODO',
1865 p_parameterlist=>l_parameter_list);
1866
1867 l_icn := wf_engine.GetItemAttrNumber(
1868 itemtype => item_type,
1869 itemkey => item_key,
1870 aname => 'PARAMETER7');
1871
1872 wf_event.AddParameterToList( p_name=>'DOCUMENT_ID',
1873 p_value=>l_icn,
1874 p_parameterlist=>l_parameter_list);
1875
1876 wf_event.AddParameterToList( p_name=>'PARTY_TYPE',
1877 p_value=>'I',
1878 p_parameterlist=>l_parameter_list);
1879
1880 wf_event.AddParameterToList( p_name=>'SEND_MODE',
1881 p_value=>'Immediate',
1882 p_parameterlist=>l_parameter_list);
1883
1884 wf_event.AddParameterToList( p_name=>'CONFIRM_STATUSLVL',
1885 p_value=>l_confirm_statuslvl,
1886 p_parameterlist=>l_parameter_list);
1887
1888 wf_event.AddParameterToList( p_name=>'CONFIRM_DESCRTN',
1889 p_value=>l_confirm_descrtn,
1890 p_parameterlist=>l_parameter_list);
1891
1892 wf_event.AddParameterToList( p_name=>'CONFIRM_DET_DESCRIPTN',
1893 p_value=>l_confirm_det_descriptn,
1894 p_parameterlist=>l_parameter_list);
1895
1896 wf_event.AddParameterToList( p_name=>'CONFIRM_DET_REASONCODE',
1897 p_value=>l_confirm_det_reasoncode,
1898 p_parameterlist=>l_parameter_list);
1899
1900 l_event_name := 'oracle.apps.gmi.api.xml.confirm';
1901
1902 wf_event.raise( p_event_name => l_event_name,
1903 p_event_key => l_event_key,
1904 p_parameters => l_parameter_list);
1905
1906 l_parameter_list.DELETE;
1907
1908 resultout := 'COMPLETE:Y';
1909
1910 END send_error_cbod;
1911
1912
1913 END Gmi_Apixml_Pkg;