1 PACKAGE BODY gmi_msca_pub AS
2 /* $Header: GMIPMSCB.pls 120.0 2005/05/25 16:12:20 appldev noship $ */
3
4
5 --- For GTIN support
6 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
7 g_gtin_code_length NUMBER := 14;
8
9
10 -- PL/SQL package to support Java MSCA for GMI
11 PROCEDURE print_debug( p_message IN VARCHAR2)
12 IS
13 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
14 BEGIN
15 inv_log_util.TRACE(p_message, 'Mobile GMI', 9);
16 -- dbms_output.put_line(p_message);
17 END print_debug;
18
19
20 FUNCTION get_opm_uom_code(x_apps_unit_meas_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
21 v_um_code SY_UOMS_MST.UM_CODE%TYPE;
22 BEGIN
23
24 Select um_code
25 Into v_um_code
26 From sy_uoms_mst
27 Where unit_of_measure = x_apps_unit_meas_lookup_code;
28
29 RETURN(v_um_code);
30
31 EXCEPTION
32 WHEN OTHERS THEN
33 raise;
34
35 END get_opm_uom_code;
36
37 -- LOV procedures :
38
39 PROCEDURE item_no_lov
40 ( x_itemNo_cursor OUT NOCOPY t_genref
41 , p_item_no IN VARCHAR2
42 , p_item_desc IN VARCHAR2
43 )
44 IS
45
46 l_cross_ref varchar2(204);
47
48
49 BEGIN
50
51 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
52
53
54 print_debug('in item LOV... item_no='||p_item_no||', desc='||p_item_desc||'.');
55
56 IF (length(NVL(p_item_no, '')) > 0)
57 THEN
58
59 OPEN x_itemNo_cursor FOR
60 SELECT item_no, item_desc1, item_id
61 , loct_ctl, lot_ctl, sublot_ctl, grade_ctl, status_ctl, dualum_ind
62 , qc_grade, lot_status, lot_indivisible, item_um, item_um2, noninv_ind
63 FROM ic_item_mst
64 WHERE UPPER(item_no) like UPPER(p_item_no)
65 AND delete_mark = 0
66
67 --- For GTIN support
68 UNION
69
70 SELECT item_no, item_desc1, item_id
71 , loct_ctl, lot_ctl, sublot_ctl, grade_ctl, status_ctl, dualum_ind
72 , qc_grade, lot_status, lot_indivisible,
73 Get_Opm_Uom_Code(mcr.uom_code) item_um,
74 item_um2, noninv_ind
75 FROM
76 mtl_cross_references mcr,
77 mtl_system_items mti,
78 ic_item_mst opi
79 WHERE
80 opi.item_no = mti.segment1
81 AND mti.inventory_item_id = mcr.inventory_item_id
82 AND mcr.cross_reference_type = g_gtin_cross_ref_type
83 AND mcr.cross_reference LIKE l_cross_ref
84 --- AND (mcr.organization_id = mti.organization_id
85 ---OR
86 --- AND mcr.org_independent_flag = 'Y'
87
88 ORDER BY item_no;
89
90 ELSE
91 -- Force to raise "NO Result Found".
92 OPEN x_itemNo_cursor FOR
93 SELECT item_no, item_desc1, item_id
94 , loct_ctl, lot_ctl, sublot_ctl, grade_ctl, status_ctl, dualum_ind
95 , qc_grade, lot_status, lot_indivisible, item_um, item_um2, noninv_ind
96 FROM ic_item_mst
97 WHERE 1=2;
98 END IF;
99
100 EXCEPTION
101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
102 THEN
103 NULL;
104
105 END item_no_lov;
106
107 PROCEDURE loct_lov
108 ( x_loct_cursor OUT NOCOPY t_genref
109 , p_loct IN VARCHAR2
110 , p_whse_code IN VARCHAR2
111 )
112 IS
113
114 /*
115 select location, loct_desc
116 from ic_loct_mst
117 where whse_code = :ic_adjs_jnl_vw.to_whse_code
118 and delete_mark = 0 and location <> :parameter.default_loct
119 UNION
120 select distinct l.location, '' loct_desc
121 from ic_loct_inv l
122 where whse_code = :ic_adjs_jnl_vw.to_whse_code
123 and not exists (select location from ic_loct_mst where location = l.location)
124 and (:ic_adjs_jnl_vw.item_loct_ctl = 2 OR :ic_adjs_jnl_vw.to_whse_loct_ctl = 2) order by 1
125 */
126
127 BEGIN
128
129 OPEN x_loct_cursor FOR
130 SELECT location, loct_desc, whse_code
131 FROM ic_loct_mst
132 WHERE whse_code LIKE p_whse_code
133 AND location <> FND_PROFILE.VALUE('IC$DEFAULT_LOCT')
134 AND location LIKE p_loct
135 AND delete_mark = 0
136 UNION
137 SELECT l.location, null, l.whse_code
138 FROM ic_loct_inv l
139 WHERE l.whse_code LIKE p_whse_code
140 AND l.location LIKE p_loct
141 AND NOT EXISTS(select location from ic_loct_mst where location = l.location)
142 ORDER BY 1;
143
144 EXCEPTION
145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
146 THEN
147 NULL;
148
149 END loct_lov;
150
151 PROCEDURE lot_lov
152 ( x_lot_cursor OUT NOCOPY t_genref
153 , p_lot IN VARCHAR2
154 , p_sublot IN VARCHAR2
155 , p_item_no IN VARCHAR2
156 , p_whse_code IN VARCHAR2
157 , p_location IN VARCHAR2
158 )
159 IS
160
161
162 /* OPM Note : LOT statement in form ICQTYED:
163 SELECT DISTINCT A.lot_no, A.sublot_no
164 FROM ic_lots_mst A, ic_loct_inv B
165 WHERE A.lot_id = B.lot_id AND A.delete_mark = 0 AND B.delete_mark = 0
166 AND B.item_id = :PARAMETER.item_id and a.item_id = b.item_id
167 AND lot_no <> :parameter.default_lot
168 UNION
169 select lot_no, sublot_no
170 from ic_lots_mst
171 where item_id = :parameter.item_id
172 and (substr(:ic_jrnl_mst.trans_type,1,3) in ('CRE','ADJ')) and delete_mark = 0
173 and lot_no <> :parameter.default_lot
174 ORDER BY 1, 2
175 */
176 BEGIN
177
178 OPEN x_lot_cursor FOR
179 SELECT DISTINCT A.lot_no, NVL(A.sublot_no, ' '), NVL(A.lot_desc, ' ')
180 , A.lot_id, A.item_id, NVL(A.qc_grade, ' '), B.lot_status
181 FROM ic_lots_mst A, ic_loct_inv B
182 WHERE A.lot_id = B.lot_id
183 AND A.lot_no LIKE p_lot
184 AND A.delete_mark = 0
185 AND B.delete_mark = 0
186 AND B.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
187 AND A.item_id = B.item_id
188 AND NVL(p_whse_code, B.whse_code) = B.whse_code
189 AND NVL(p_location, B.location) = B.location
190 UNION
191 SELECT DISTINCT A.lot_no, NVL(A.sublot_no, ' '), NVL(A.lot_desc, ' ')
192 , A.lot_id, A.item_id, NVL(A.qc_grade, ' '), null
193 FROM ic_lots_mst A
194 WHERE A.lot_no LIKE p_lot
195 AND A.delete_mark = 0
196 AND A.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
197 AND A.lot_id > 0
198 ORDER BY 1, 2;
199
200
201 EXCEPTION
202 WHEN OTHERS
203 THEN
204 print_debug('in lot LOV unexp error');
205 NULL;
206
207 END lot_lov;
208
209 PROCEDURE orgn_lov
210 ( x_orgn_cursor OUT NOCOPY t_genref
211 , p_orgn IN VARCHAR2
212 , p_user_id IN NUMBER
213 )
214 IS
215 BEGIN
216
217 OPEN x_orgn_cursor FOR
218 SELECT m.orgn_code, m.orgn_name, m.co_code
219 FROM sy_orgn_mst m, sy_orgn_usr u
220 WHERE u.user_id = p_user_id
221 AND u.orgn_code = m.orgn_code
222 AND m.orgn_code like p_orgn
223 AND m.delete_mark = 0
224 ORDER BY m.orgn_code;
225
226 EXCEPTION
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
228 THEN
229 NULL;
230
231 END orgn_lov;
232
233 PROCEDURE reason_lov
234 ( x_reason_cursor OUT NOCOPY t_genref
235 , p_reason IN VARCHAR2
236 , p_doc_type IN VARCHAR2
237 )
238 IS
239 BEGIN
240
241 -- in TDD closed issues :
242 -- The reason code LOV must be the same in Forms UI and Mobile
243 -- Restriction on increase/decrease are only for ADJI.
244
245 -- NEED TO REALLY CHECK THE LOV IN DESKTOP
246 -- 11.5.9
247 -- 11.5.10
248
249 OPEN x_reason_cursor FOR
250 SELECT reason_code, reason_desc1, reason_type
251 FROM sy_reas_cds
252 WHERE delete_mark = 0
253 AND ((NVL(FND_PROFILE.VALUE('GMA_REASON_CODE_SECURITY'), 'N') = 'N'
254 OR reason_code IN (SELECT reason_code FROM gma_reason_code_security
255 WHERE (doc_type = p_doc_type or doc_type IS NULL)
256 AND (responsibility_id = FND_GLOBAL.RESP_id OR responsibility_id IS NULL)) ) )
257 AND reason_code LIKE UPPER(p_reason)
258 ORDER BY 1 ;
259
260 EXCEPTION
261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
262 THEN
263 NULL;
264
265 END reason_lov;
266
267 PROCEDURE sublot_lov
268 ( x_sublot_cursor OUT NOCOPY t_genref
269 , p_sublot IN VARCHAR2
270 , p_lot IN VARCHAR2
271 , p_item_no IN VARCHAR2
272 )
273 IS
274 BEGIN
275
276 print_debug('in sublot item='||p_item_no||', lot='||p_lot||', sublot='||p_sublot||'.');
277 OPEN x_sublot_cursor FOR
278 SELECT DISTINCT A.lot_no, NVL(A.sublot_no, ' '), NVL(A.lot_desc, ' ')
279 , A.lot_id, A.item_id, NVL(A.qc_grade, ' '), B.lot_status
280 FROM ic_lots_mst A
281 , ic_loct_inv B
282 WHERE B.lot_id = A.lot_id
283 AND B.item_id = A.item_id
284 AND A.sublot_no like p_sublot
285 AND A.lot_no = p_lot
286 AND A.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
287 AND B.delete_mark = 0
288 AND A.delete_mark = 0
289 ORDER BY 1, 2;
290
291 EXCEPTION
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
293 THEN
294 NULL;
295
296 END sublot_lov;
297
298 PROCEDURE uom_lov
299 ( x_uom_cursor OUT NOCOPY t_genref
300 , p_uom IN VARCHAR2
301 , p_item_no IN VARCHAR2)
302 IS
303
304 BEGIN
305
306 print_debug('in uom_lov item_no='||p_item_no||'...');
307 OPEN x_uom_cursor FOR
308 SELECT u.uom_code
309 FROM sy_uoms_mst u
310 , ic_item_cnv i
311 WHERE i.um_type = u.um_type
312 AND u.uom_code like p_uom
313 AND i.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
314 AND i.delete_mark = 0
315 AND u.delete_mark = 0
316 UNION ALL
317 SELECT item_um
318 FROM ic_item_mst
319 WHERE item_no = p_item_no
320 AND item_um like p_uom
321 ORDER BY 1;
322
323 EXCEPTION
324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
325 THEN
326 NULL;
327
328 END uom_lov;
329
330 PROCEDURE whse_lov
331 ( x_subInv_cursor OUT NOCOPY t_genref
332 , p_whse_code IN VARCHAR2
333 , p_orgn_code IN VARCHAR2
334 , p_user_id IN NUMBER
335 )
336 IS
337 BEGIN
338
339 -- In Item Inquery the organization is not displayed (null value)
340 IF ( NVL(p_orgn_code, ' ') = ' ' )
341 THEN
342 IF (length( NVL(p_whse_code, '')) > 0)
343 THEN
344 OPEN x_subInv_cursor FOR
345 SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
346 FROM ic_whse_mst w, sy_orgn_usr u
347 WHERE u.orgn_code = w.orgn_code
348 AND u.user_id = p_user_id
349 AND w.whse_code like p_whse_code
350 AND w.delete_mark = 0
351 ORDER BY whse_code;
352 ELSE
353 -- This query forces the return of No Result Found.
354 OPEN x_subInv_cursor FOR
355 SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
356 FROM ic_whse_mst w
357 WHERE 1=2;
358 END IF;
359 ELSE
360 IF (length( NVL(p_whse_code, '')) > 0)
361 THEN
362 OPEN x_subInv_cursor FOR
363 SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
364 FROM ic_whse_mst w, sy_orgn_usr u
365 WHERE u.orgn_code = w.orgn_code
366 AND u.orgn_code = p_orgn_code
367 AND u.user_id = p_user_id
368 AND w.whse_code like p_whse_code
369 AND w.delete_mark = 0
370 ORDER BY whse_code;
371 ELSE
372 -- This query forces the return of No Result Found.
373 OPEN x_subInv_cursor FOR
374 SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
375 FROM ic_whse_mst w
376 WHERE 1=2;
377 END IF;
378 END IF;
379 EXCEPTION
380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
381 THEN
382 NULL;
383
384 END whse_lov;
385
386 PROCEDURE to_whse_lov
387 ( x_subInv_cursor OUT NOCOPY t_genref
388 , p_whse_code IN VARCHAR2
389 , p_from_whse_code IN VARCHAR2
390 , p_orgn_code IN VARCHAR2
391 , p_user_id IN NUMBER
392 )
393 IS
394 BEGIN
395
396 -- It is possible to transfer into the same whse :
397 -- AND w.whse_code <> p_from_whse_code
398 -- bug 4033866 : replaced sy_orgn_usr
399 -- and removed the user_id restriction.
400 -- WHERE u.orgn_code = w.orgn_code
401 -- AND u.orgn_code = p_orgn_code
402 -- AND u.user_id = p_user_id
403 IF (length( NVL(p_whse_code, '')) > 0)
404 THEN
405 OPEN x_subInv_cursor FOR
406 SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
407 FROM ic_whse_mst w
408 WHERE w.whse_code like p_whse_code
409 AND w.delete_mark = 0
410 ORDER BY whse_code;
411 ELSE
412 -- This query forces the return of No Result Found.
413 OPEN x_subInv_cursor FOR
414 SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
415 FROM ic_whse_mst w
416 WHERE 1=2;
417 END IF;
418
419 EXCEPTION
420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
421 THEN
422 NULL;
423
424 END to_whse_lov;
425
426 PROCEDURE to_loct_lov
427 ( x_loct_cursor OUT NOCOPY t_genref
428 , p_loct IN VARCHAR2
429 , p_whse_code IN VARCHAR2
430 , p_from_whse IN VARCHAR2
431 , p_from_loct IN VARCHAR2
432 )
433 IS
434
435 /*
436 select location, loct_desc
437 from ic_loct_mst
438 where whse_code = :ic_adjs_jnl_vw.to_whse_code
439 and delete_mark = 0 and location <> :parameter.default_loct UNION
440 select distinct l.location, '' loct_desc
441 from ic_loct_inv l
442 where whse_code = :ic_adjs_jnl_vw.to_whse_code
443 and not exists (select location from ic_loct_mst where location = l.location)
444 and (:ic_adjs_jnl_vw.item_loct_ctl = 2 OR :ic_adjs_jnl_vw.to_whse_loct_ctl = 2) order by 1
445 */
446
447 BEGIN
448
449 OPEN x_loct_cursor FOR
450 SELECT location, loct_desc, whse_code
451 FROM ic_loct_mst
452 WHERE whse_code LIKE p_whse_code
453 AND location <> FND_PROFILE.VALUE('IC$DEFAULT_LOCT')
454 AND location LIKE p_loct
455 AND ( (location <> p_from_loct AND whse_code = p_from_whse)
456 OR (whse_code <> p_from_whse) )
457 AND delete_mark = 0
458 UNION
459 SELECT l.location, null, l.whse_code
460 FROM ic_loct_inv l
461 WHERE l.whse_code LIKE p_whse_code
462 AND l.location LIKE p_loct
463 AND ( (l.location <> p_from_loct AND l.whse_code = p_from_whse)
464 OR (l.whse_code <> p_from_whse) )
465 AND NOT EXISTS(select location from ic_loct_mst where location = l.location)
466 ORDER BY 1;
467
468 EXCEPTION
469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
470 THEN
471 NULL;
472
473 END to_loct_lov;
474
475 -- End of LOV Procedures
476
477 -- Begin of other procedures
478 PROCEDURE create_transaction
479 ( p_user_name IN VARCHAR2
480 , p_doc_type IN VARCHAR2
481 , p_item_no IN VARCHAR2
482 , p_whse_code IN VARCHAR2
483 , p_orgn_code IN VARCHAR2
484 , p_co_code IN VARCHAR2
485 , p_location IN VARCHAR2
486 , p_lot_no IN VARCHAR2
487 , p_sublot_no IN VARCHAR2
488 , p_qc_grade IN VARCHAR2
489 , p_lot_status IN VARCHAR2
490 , p_reason_code IN VARCHAR2
491 , p_trans_qty1 IN NUMBER
492 , p_trans_UOM1 IN VARCHAR2
493 , p_trans_qty2 IN NUMBER
494 , p_trans_UOM2 IN VARCHAR2
495 , p_to_whse_code IN VARCHAR2
496 , p_to_location IN VARCHAR2
497 , x_return_value OUT NOCOPY NUMBER
498 , x_message OUT NOCOPY VARCHAR2)
499
500 IS
501
502 l_return_status VARCHAR2(1);
503 l_msg_count NUMBER;
504 l_msg_data VARCHAR2(240);
505 l_message VARCHAR2(300);
506 l_count NUMBER;
507 l_bool BOOLEAN;
508 l_trans_type NUMBER;
509 l_lot_no VARCHAR2(150);
510 l_sublot_no VARCHAR2(150);
511 l_location VARCHAR2(150);
512 l_qc_grade VARCHAR2(5);
513 l_lot_status VARCHAR2(5);
514
515 l_qty_rec GMIGAPI.qty_rec_typ;
516
517 l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
518 l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
519 l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
520
521 BEGIN
522 print_debug('in Create_Transaction doc_type='||p_doc_type||'...');
523 print_debug('in Create_Transaction item='||p_item_no||', whse='||p_whse_code||', orgn='||p_orgn_code||', co='||p_co_code);
524 print_debug('in Create_Transaction lot='||p_lot_no||', status='||p_lot_status||', qty='||p_trans_qty1||', qty2='||p_trans_qty2);
525 print_debug('in Create_Transaction sublot='||p_sublot_no||', location='||p_location||', grade='||p_qc_grade);
526
527 l_bool := GMIGUTL.setup(p_user_name);
528
529 IF (p_doc_type = 'ADJI')
530 THEN
531 l_trans_type := 2;
532 ELSIF (p_doc_type = 'TRNI')
533 THEN
534 l_trans_type := 3;
535 END IF;
536
537 IF ( NVL(p_lot_no, '') IN ('NULL', '') )
538 THEN
539 l_lot_no := NULL;
540 ELSE
541 l_lot_no := p_lot_no;
542 END IF;
543
544 IF ( NVL(p_sublot_no, '') IN ('NULL', '') )
545 THEN
546 l_sublot_no := NULL;
547 ELSE
548 l_sublot_no := p_sublot_no;
549 END IF;
550
551 IF ( NVL(p_location, '') IN ('NULL', '') )
552 THEN
553 l_location := NULL;
554 ELSE
555 l_location := p_location;
556 END IF;
557
558 IF ( NVL(p_qc_grade, '') IN ('NULL', '') )
559 THEN
560 l_qc_grade := NULL;
561 ELSE
562 l_qc_grade := p_qc_grade;
563 END IF;
564
565 IF ( NVL(p_lot_status, '') IN ('NULL', '') )
566 THEN
567 l_lot_status := NULL;
568 ELSE
569 l_lot_status := p_lot_status;
570 END IF;
571
572 print_debug('in Create_Transaction local lot='||l_lot_no||', status='||l_lot_status||'.');
573 print_debug('in Create_Transaction local sublot='||l_sublot_no||', location='||l_location||', grade='||l_qc_grade);
574
575 l_qty_rec.trans_type := l_trans_type;
576 l_qty_rec.item_no := p_item_no;
577 --l_qty_rec.journal_no ic_jrnl_mst.journal_no%TYPE
578 l_qty_rec.from_whse_code := p_whse_code;
579 IF (p_doc_type = 'TRNI')
580 THEN
581 l_qty_rec.to_whse_code := p_to_whse_code;
582 l_qty_rec.to_location := p_to_location;
583 END IF;
584 l_qty_rec.item_um := p_trans_UOM1;
585 l_qty_rec.item_um2 := p_trans_UOM2;
586 l_qty_rec.lot_no := l_lot_no;
587 l_qty_rec.sublot_no := l_sublot_no;
588 l_qty_rec.from_location := l_location;
589 l_qty_rec.trans_qty := p_trans_qty1;
590 l_qty_rec.trans_qty2 := p_trans_qty2;
591 l_qty_rec.qc_grade := l_qc_grade;
592 l_qty_rec.lot_status := l_lot_status;
593 l_qty_rec.co_code := p_co_code;
594 l_qty_rec.orgn_code := p_orgn_code;
595 --l_qty_rec.trans_date ic_tran_cmp.trans_date%TYPE DEFAULT SYSDATE
596 l_qty_rec.reason_code := p_reason_code;
597 l_qty_rec.user_name := p_user_name;
598 l_qty_rec.journal_comment := p_doc_type||' PLSQL';
599 --l_qty_rec.attribute1 ic_jrnl_mst.attribute1%TYPE DEFAULT NULL
600 --l_qty_rec.attribute2 ic_jrnl_mst.attribute2%TYPE DEFAULT NULL
601 --l_qty_rec.attribute30 ic_jrnl_mst.attribute30%TYPE DEFAULT NULL
602 --l_qty_rec.attribute_category ic_jrnl_mst.attribute_category%TYPE DEFAULT NULL
603 --l_qty_rec.acctg_unit_no VARCHAR2(240) DEFAULT NULL
604 --l_qty_rec.acct_no VARCHAR2(240) DEFAULT NULL
605 --l_qty_rec.txn_type VARCHAR2(3) DEFAULT NULL
606 --l_qty_rec.journal_ind VARCHAR2(1) DEFAULT NULL
607 --l_qty_rec.move_entire_qty VARCHAR2(2) DEFAULT 'Y' --BUG#2861715 Sastry
608
609
610 print_debug('in Create_Transaction calling GMIPAPI.Inventory_Posting');
611
612 GMIPAPI.Inventory_Posting
613 ( p_api_version => 3.0
614 , p_init_msg_list => FND_API.G_TRUE
615 , p_commit => FND_API.G_FALSE
616 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
617 , p_qty_rec => l_qty_rec
618 , x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
619 , x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
620 , x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
621 , x_return_status => l_return_status
622 , x_msg_count => l_msg_count
623 , x_msg_data => l_msg_data);
624
625 print_debug('in Create_Transaction after GMIPAPI.Inventory_Posting');
626
627 IF l_return_status = fnd_api.g_ret_sts_success
628 THEN
629 print_debug('SUCCESS....');
630 x_return_value := 0;
631 COMMIT;
632 ELSE
633 print_debug('ERROR count='|| l_msg_count);
634 IF l_msg_count > 0
635 THEN
636 FOR i IN 1..l_msg_count
637 LOOP
638 FND_MSG_PUB.get
639 ( p_msg_index => i
640 , p_data => l_message
641 , p_encoded => fnd_api.g_false
642 , p_msg_index_out => l_count
643 );
644
645 IF i = 1
646 THEN
647 x_message := l_message;
648 END IF;
649
650 END LOOP;
651 END IF;
652
653 -- bug 4125917 : replaced CASE by IF (compatibility with 8i RDBMS)
654 --CASE l_return_status
655 -- WHEN 'E'
656 IF ( l_return_status = 'E')
657 THEN
658 x_return_value := -1;
659 ELSIF ( l_return_status = 'U')
660 THEN
661 x_return_value := -2;
662 ELSE
663 x_return_value := -3;
664 END IF;
665
666 ROLLBACK;
667 END IF;
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 print_debug('in Create_Transaction OTHERS='||SQLERRM);
672 END create_transaction;
673
674 PROCEDURE get_lot_status
675 ( p_item_id IN NUMBER
676 , p_whse_code IN VARCHAR2
677 , p_location IN VARCHAR2
678 , p_lot_id IN NUMBER
679 , p_lot_status IN VARCHAR2
680 , x_lot_status OUT NOCOPY VARCHAR2
681 )
682 IS
683
684 CURSOR get_lot_stat( item IN NUMBER, whse IN VARCHAR2, loct IN VARCHAR2, lot IN NUMBER, stat IN VARCHAR2) IS
685 SELECT ili.lot_status
686 FROM ic_lots_sts sts
687 , ic_loct_inv ili
688 WHERE ili.lot_status = sts.lot_status
689 AND sts.lot_status LIKE stat
690 AND ili.location = loct
691 AND ili.item_id = item
692 AND NVL(lot, ili.lot_id) = ili.lot_id
693 AND ili.delete_mark = 0
694 AND sts.delete_mark = 0
695 ORDER BY ili.lot_status;
696
697 BEGIN
698 OPEN get_lot_stat( p_item_id, p_whse_code, p_location, p_lot_id, p_lot_status);
699 FETCH get_lot_stat
700 INTO x_lot_status;
701
702 IF (get_lot_stat%NOTFOUND)
703 THEN
704 CLOSE get_lot_stat;
705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
706 END IF;
707
708 CLOSE get_lot_stat;
709
710 EXCEPTION
711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
712 THEN
713 x_lot_status := NULL;
714
715 END get_lot_status;
716
717 END gmi_msca_pub;
[Home] [Help]