[Home] [Help]
PACKAGE BODY: APPS.GMI_OM_UTIL
Source
1 PACKAGE BODY GMI_OM_UTIL AS
2 /* $Header: GMIOMUTB.pls 115.6 2004/03/08 17:06:34 uphadtar noship $ */
3 /*
4 +=========================================================================+
5 | Copyright (c) 2000 Oracle Corporation |
6 | TVP, Reading, England |
7 | All rights reserved |
8 +=========================================================================+
9 | FILENAME |
10 | GMIOMUTB.pls |
11 | |
12 | DESCRIPTION |
13 | This package contains generic utilities relating to OPM and OM. |
14 | |
15 | |
16 +=========================================================================+
17 */
18
19 /*
20 ==========================================================================
21 p_original_line_rec is RMA line record.
22 p_reference_line_rec is referenced sales order line record for which
23 RMA is being created.
24 ==========================================================================
25 */
26 PROCEDURE GMI_GET_RMA_LOTS_QTY
27 ( p_original_line_rec IN OE_Order_PUB.Line_Rec_Type
28 , p_reference_line_rec IN OE_Order_PUB.Line_Rec_Type
29 , p_x_lot_serial_tbl IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Tbl_Type
30 , x_return_status OUT NOCOPY VARCHAR2
31 ) IS
32
33 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
34 l_sum_return_qty NUMBER := 0;
35 l_number NUMBER := 0;
36 l_trans_qty NUMBER := 0;
37 l_trans_qty2 NUMBER := 0; -- OPM 2380194
38 l_lot_trxn_qty NUMBER := 0; -- Bug 3387203
39 l_lot_trxn_qty2 NUMBER := 0; -- Bug 3387203
40 l_rma_qty NUMBER := 0;
41 l_trans_apps_um VARCHAR2(3);
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(100);
44 err_num NUMBER;
45 err_msg VARCHAR2(100);
46
47 CURSOR item_dtl IS
48 SELECT iim.lot_ctl, iim.dualum_ind
49 FROM ic_item_mst iim, mtl_system_items msi
50 WHERE msi.inventory_item_id = p_reference_line_rec.inventory_item_id
51 AND msi.organization_id = p_reference_line_rec.ship_from_org_id
52 AND msi.segment1 = iim.item_no;
53
54 rec_item_dtl item_dtl%ROWTYPE;
55
56 CURSOR trans_dtl IS
57 SELECT itp.lot_id, ilm.lot_no, ilm.sublot_no, ABS(itp.trans_qty) trans_qty, itp.trans_um,
58 ABS(itp.trans_qty2) trans_qty2, itp.trans_um2
59 FROM ic_tran_pnd itp, ic_lots_mst ilm
60 WHERE itp.doc_type ='OMSO'
61 AND itp.line_id = p_original_line_rec.reference_line_id
62 AND itp.delete_mark = 0
63 AND itp.completed_ind = 1
64 AND itp.lot_id <> 0
65 AND itp.trans_qty <> 0
66 AND itp.staged_ind = 1
67 AND itp.line_detail_id is not null
68 AND ilm.item_id = itp.item_id
69 AND ilm.lot_id = itp.lot_id
70 ORDER BY trans_id;
71
72
73 BEGIN
74
75 GMI_RESERVATION_UTIL.Println('In Procedure GMI_OM_UTIL.GMI_GET_RMA_LOTS_QTY');
76
77 l_rma_qty := p_original_line_rec.ordered_quantity;
78
79 /* Clear Table */
80 l_Lot_serial_tbl.DELETE;
81
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 GMI_RESERVATION_UTIL.Println('Getting item details for Apps Item ID '
85 ||to_char(p_reference_line_rec.inventory_item_id));
86 OPEN item_dtl;
87 FETCH item_dtl into rec_item_dtl;
88 CLOSE item_dtl;
89
90 IF rec_item_dtl.lot_ctl = 1 THEN
91 GMI_RESERVATION_UTIL.Println('Fetching lot quantities for order line id '||
92 to_char(p_original_line_rec.reference_line_id));
93 FOR r_trans_dtl IN trans_dtl LOOP
94
95
96 /* Exit the loop if lot item qty reaches line return qty. */
97 IF (l_sum_return_qty = l_rma_qty) THEN
98 EXIT;
99 END IF;
100
101 IF (l_number = 0) THEN /* get apps uom just once */
102 GMI_Reservation_Util.Get_AppsUOM_from_OPMUOM(
103 p_OPM_UOM => r_trans_dtl.trans_um
104 , x_Apps_UOM => l_trans_apps_um
105 , x_return_status => x_return_status
106 , x_msg_count => l_msg_count
107 , x_msg_data => l_msg_data);
108 END IF;
109
110 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
111 GMI_RESERVATION_UTIL.Println('Unable to Get_AppsUOM_from_OPMUOM');
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 EXIT;
114 END IF;
115
116 /* if item primary uom and rma uom are different convert trans qty */
117 IF (l_trans_apps_um <> p_original_line_rec.order_quantity_uom) THEN
118 l_trans_qty := GMI_RESERVATION_UTIL.Get_Opm_converted_qty
119 ( p_apps_item_id => p_reference_line_rec.inventory_item_id
120 ,p_organization_id => p_reference_line_rec.ship_from_org_id
121 ,p_apps_from_uom => l_trans_apps_um
122 ,p_apps_to_uom => p_original_line_rec.order_quantity_uom
123 ,p_original_qty => r_trans_dtl.trans_qty );
124 ELSE
125 l_trans_qty := r_trans_dtl.trans_qty;
126 END IF;
127
128 IF (OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' ) THEN -- OPM 2380194
129 l_trans_qty2 := r_trans_dtl.trans_qty2;
130 END IF;
131
132 l_sum_return_qty := l_sum_return_qty + l_trans_qty;
133 l_number := l_number + 1;
134
135 /* lot indivisibility is not considered here. */
136 /* need to assign partial lot qty when l_rma_qty is less shipped qty. */
137 IF (l_sum_return_qty > l_rma_qty) THEN
138 l_lot_trxn_qty := l_rma_qty - (l_sum_return_qty - l_trans_qty);
139 l_sum_return_qty := l_rma_qty; /* this assignment is for exiting from loop */
140
141 /* need to derive secondary qty because partial lot qty is considered */
142 IF ( OE_CODE_CONTROL.Get_Code_Release_Level >= '110510') THEN
143 IF (rec_item_dtl.dualum_ind > 0 ) THEN
144 l_lot_trxn_qty2 := GMI_RESERVATION_UTIL.Get_Opm_converted_qty
145 ( p_apps_item_id => p_reference_line_rec.inventory_item_id
146 ,p_organization_id => p_reference_line_rec.ship_from_org_id
147 ,p_apps_from_uom => p_original_line_rec.order_quantity_uom
148 ,p_apps_to_uom => p_original_line_rec.ordered_quantity_uom2
149 ,p_original_qty => l_lot_trxn_qty
150 ,p_lot_id => r_trans_dtl.lot_id );
151 ELSE
152 l_lot_trxn_qty2 := l_trans_qty2;
153 END IF;
154 END IF;
155 ELSE
156 l_lot_trxn_qty := l_trans_qty;
157 IF (OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' ) THEN
158 l_lot_trxn_qty2 := l_trans_qty2;
159 END IF;
160 END IF;
161
162 GMI_RESERVATION_UTIL.Println('lot_no = '||r_trans_dtl.lot_no);
163 GMI_RESERVATION_UTIL.Println('sublot_no = '||r_trans_dtl.sublot_no);
164 GMI_RESERVATION_UTIL.Println('lot_trxn_qty = '||to_char(l_lot_trxn_qty));
165 GMI_RESERVATION_UTIL.Println('lot_trxn_qty2 = '||to_char(l_lot_trxn_qty2));
166
167 l_lot_serial_tbl(l_number) := OE_ORDER_PUB.G_MISS_LOT_SERIAL_REC;
168 l_lot_serial_tbl(l_number).lot_number := r_trans_dtl.lot_no;
169 l_lot_serial_tbl(l_number).quantity := l_lot_trxn_qty;
170
171 IF (OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' ) THEN -- OPM 2380194
172 l_lot_serial_tbl(l_number).sublot_number := r_trans_dtl.sublot_no;
173 l_lot_serial_tbl(l_number).quantity2 := l_lot_trxn_qty2;
174 END IF;
175
176 END LOOP;
177
178 IF (l_number = 0) THEN
179 /* commenting following line because referencing unshipped line was giving errors. May be its an OM issue */
180 /* x_return_status := FND_API.G_RET_STS_ERROR; */
181 GMI_RESERVATION_UTIL.Println('Could not fetch lot quantities for order line id '||
182 to_char(p_original_line_rec.reference_line_id));
183 END IF;
184
185 END IF; /* IF rec_item_dtl.lot_ctl = 1 */
186
187 p_x_lot_serial_tbl := l_lot_serial_tbl;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 GMI_RESERVATION_UTIL.Println('Raised When Others in GMI_RMA_LOT_QTY');
193 err_num := SQLCODE;
194 err_msg := SUBSTRB(SQLERRM, 1, 100);
195 GMI_RESERVATION_UTIL.Println(to_char(err_num)||'=>'||err_msg);
196
197 END GMI_GET_RMA_LOTS_QTY;
198
199 FUNCTION GMI_GET_SECONDARY_QTY
200 (
201 p_delivery_detail_id IN NUMBER,
202 p_primary_quantity IN NUMBER
203 ) RETURN NUMBER IS
204
205 l_line_id NUMBER;
206 l_lot_id NUMBER;
207 l_organization_id NUMBER;
208 l_inventory_item_id NUMBER;
209 l_apps_from_uom VARCHAR2(5);
210 l_apps_to_uom VARCHAR2(5);
211 l_return_status VARCHAR2(30);
212 l_msg_count NUMBER;
213 l_msg_data VARCHAR2(5);
214 l_opm_from_uom VARCHAR2(5);
215 l_opm_to_uom VARCHAR2(5);
216 l_converted_qty NUMBER;
217
218 CURSOR Get_wdd_rec IS
219 SELECT * from wsh_delivery_details
220 where delivery_detail_id = p_delivery_detail_id;
221
222 WDD_REC Get_wdd_rec%rowtype;
223
224 CURSOR Get_lot_id IS
225 SELECT LOT_ID from ic_tran_pnd
226 where line_detail_id = p_delivery_detail_id and
227 line_id = l_line_id and
228 doc_type = 'OMSO' and
229 delete_mark = 0 and
230 staged_ind = 1;
231 BEGIN
232
233 OPEN Get_wdd_rec;
234 FETCH Get_wdd_rec into WDD_REC;
235 IF Get_wdd_rec%FOUND THEN
236 l_inventory_item_id := WDD_REC.INVENTORY_ITEM_ID;
237 l_line_id := WDD_REC.SOURCE_LINE_ID;
238 l_organization_id := WDD_REC.ORGANIZATION_ID;
239 l_apps_from_uom := WDD_REC.SRC_REQUESTED_QUANTITY_UOM;
240 l_apps_to_uom := WDD_REC.SRC_REQUESTED_QUANTITY_UOM2;
241 END IF;
242
243 CLOSE Get_wdd_rec;
244
245 IF l_apps_to_uom IS NULL THEN
246 RETURN NULL;
247 END IF;
248
249
250 OPEN Get_lot_id;
251 FETCH Get_lot_id into l_lot_id;
252 CLOSE Get_lot_id;
253
254 l_converted_qty := GMI_Reservation_Util.Get_Opm_converted_qty
255 (
256 l_inventory_item_id,
257 l_organization_id,
258 l_apps_from_uom,
259 l_apps_to_uom,
260 p_primary_quantity,
261 nvl(l_lot_id, 0)
262 );
263 RETURN l_converted_qty;
264
265 END GMI_GET_SECONDARY_QTY;
266
267
268 END GMI_OM_UTIL;