1 PACKAGE BODY GMI_OM_UTILITIES_PKG AS
2 /* $Header: GMIUTOMB.pls 120.0 2005/05/25 16:19:12 appldev noship $ */
3 /* ===========================================================================
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 ===========================================================================
8 | FILENAME |
9 | GMIUTOMB.pls |
10 | |
11 | DESCRIPTION |
12 | |
13 | Body of package GMI_OM_UTILITIES_PKG |
14 | |
15 | NOTES |
16 | |
17 | HISTORY |
18 | |
19 | 17-NOV-04 Created |
20 | 17-NOV-04 parkumar Added Functionality for BackOrder and Delete |
21 | Allocations for a Move Order Line |
22 | - Delete_Alloc_BackOrder_MO_Line |
23 ===========================================================================
24 */
25
26 /* Global constant holding the package name */
27
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMI_OM_UTILITIES_PKG';
29
30 -- Delete_Alloc_BackOrder_MO_Line
31 -- Bug 3874270
32 -- This Function deletes Allocations for a Move Order Line if called with
33 -- p_mode = 'DA' and Backorders a Move Order Line if called with
34 -- p_mode = 'BO'.
35 -- If any allocations exist for the move order line, then they are deleted
36 -- All the Delivery Detail Lines are backordered and screen refreshed.
37 -- The move order line is closed
38 -- The order line status remains unchanged
39
40
41 FUNCTION Delete_Alloc_BackOrder_MO_Line(
42 p_txn_source_line_id IN NUMBER,
43 p_line_id IN NUMBER,
44 p_mode IN VARCHAR2)
45 RETURN BOOLEAN IS
46
47 -- p_mode : 'DA' For Delete Allocations, 'BO' For BackOrdering
48
49 l_IC$DEFAULT_LOCT VARCHAR2(255) DEFAULT NVL(FND_PROFILE.VALUE('IC$DEFAULT_LOCT'),' ') ;
50 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
51 l_txn_source_line_id NUMBER := p_txn_source_line_id;
52 l_line_id NUMBER := p_line_id;
53
54 CURSOR Wdd_Cur IS
55 SELECT move_order_line_id
56 ,delivery_detail_id
57 ,source_header_id
58 ,source_line_id
59 ,released_status
60 FROM wsh_delivery_details
61 WHERE source_line_id = l_txn_source_line_id
62 AND move_order_line_id = l_line_id
63 AND released_status = 'S'
64 AND source_code = 'OE';
65
66
67 CURSOR Get_Trans_Id_Cur(p_delivery_detail_id IN NUMBER) IS
68 SELECT trans_id ,line_id, trans_qty, trans_qty2
69 FROM ic_tran_pnd
70 WHERE line_id = l_txn_source_line_id
71 AND line_detail_id = p_delivery_detail_id
72 AND doc_type = 'OMSO'
73 AND completed_ind = 0
74 AND delete_mark = 0
75 AND staged_ind <> 1
76 AND ( lot_id <> 0 OR location <> l_IC$DEFAULT_LOCT);
77
78 CURSOR Get_Default_Trans(p_line_id ic_tran_pnd.line_id%TYPE) IS
79 SELECT trans_id
80 FROM ic_tran_pnd
81 WHERE line_id = p_line_id
82 AND line_detail_id IS NULL
83 AND doc_type = 'OMSO'
84 AND delete_mark = 0
85 AND completed_ind = 0
86 AND staged_ind = 0
87 AND ( lot_id = 0 AND location = l_IC$DEFAULT_LOCT);
88
89 l_in_tran_rec gmi_trans_engine_pub.ictran_rec;
90 l_out_tran_rec gmi_trans_engine_pub.ictran_rec;
91 l_default_trans_id NUMBER;
92 wdd_row wdd_cur%ROWTYPE;
93 get_trans_id_row get_trans_id_cur%ROWTYPE;
94 l_return_status VARCHAR2(1);
95 l_tran_row IC_TRAN_PND%ROWTYPE;
96 l_msg_count NUMBER :=0;
97 l_msg_data VARCHAR2(2000) := '';
98 l_mode VARCHAR2(2);
99 l_api_return_status VARCHAR2(10);
100 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
101 l_mo_line GMI_Move_Order_Global.mo_line_rec;
102 l_organization_id IC_TXN_REQUEST_LINES.organization_id%TYPE ;
103 l_quantity NUMBER := 0;
104 l_transaction_quantity NUMBER := 0;
105 l_secondary_quantity NUMBER := 0;
106 l_transaction_quantity2 NUMBER := 0;
107 l_allocations_Not_exists NUMBER := 0;
108
109 l_trans_qty NUMBER := 0;
110 l_trans_qty2 NUMBER := 0;
111
112
113 BEGIN
114 gmi_reservation_util.println('In Function Delete_Alloc_BackOrder_MO_Line');
115
116 /* Standard begin of API savepoint
117 ===========================================*/
118 SAVEPOINT Delete_Alloc_BackOrder_MO_Line;
119
120 l_mode := p_mode;
121 l_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 OPEN wdd_cur;
124 LOOP
125 FETCH wdd_cur INTO wdd_row;
126 EXIT WHEN wdd_cur%NOTFOUND;
127 l_trans_qty := 0;
128 l_trans_qty2 := 0;
129
130 OPEN get_trans_id_cur(wdd_row.delivery_detail_id);
131 LOOP
132 FETCH get_trans_id_cur INTO get_trans_id_row;
133 EXIT WHEN get_trans_id_cur%NOTFOUND;
134
135 l_tran_rec.trans_id := get_trans_id_row.trans_id;
136 gmi_reservation_util.println('In Function Delete_Alloc_BackOrder_MO_Line Before delete_pending_transaction');
137 GMI_TRANS_ENGINE_PUB.delete_pending_transaction (
138 1
139 , FND_API.G_FALSE
140 , FND_API.G_FALSE
141 , FND_API.G_VALID_LEVEL_FULL
142 , l_tran_rec
143 , l_tran_row
144 , l_return_status
145 , l_msg_count
146 , l_msg_data);
147
148 gmi_reservation_util.println('Return Status from delete_pending_transaction'|| l_return_status);
149
150 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
151 gmi_reservation_util.println('Error Status from delete_pending_transaction');
152 raise FND_API.G_EXC_ERROR;
153 END IF;
154
155 l_trans_qty := nvl(l_trans_qty,0) + nvl(abs(get_trans_id_row.trans_qty),0);
156 l_trans_qty2 := nvl(l_trans_qty2,0) + nvl(abs(get_trans_id_row.trans_qty2),0);
157
158 END LOOP;
159 CLOSE get_trans_id_cur;
160
161 IF l_mode = 'BO' THEN
162 SELECT organization_id
163 INTO l_organization_id
164 FROM IC_TXN_REQUEST_LINES
165 WHERE LINE_ID = l_line_id;
166
167 l_shipping_attr(1).source_header_id := wdd_row.source_header_id;
168 l_shipping_attr(1).source_line_id := wdd_row.source_line_id;
169 l_shipping_attr(1).ship_from_org_id := l_organization_id;
170 l_shipping_attr(1).released_status := wdd_row.released_status;
171 l_shipping_attr(1).delivery_detail_id := wdd_row.delivery_detail_id;
172 l_shipping_attr(1).action_flag := 'B';
173
174 WSH_INTERFACE.Update_Shipping_Attributes (
175 p_source_code => 'INV'
176 ,p_changed_attributes => l_shipping_attr
177 ,x_return_status => l_api_return_status
178 );
179 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
180 gmi_reservation_util.println('Error Status from WSH_INTERFACE.Update_Shipping_Attributes');
181 raise FND_API.G_EXC_ERROR;
182 END IF;
183
184 END IF; /* IF ( l_mode = 'BO') THEN */
185
186 END LOOP; /* wdd_cur */
187
188 CLOSE wdd_cur;
189
190 IF (l_mode = 'BO') THEN
191 UPDATE IC_TXN_REQUEST_LINES
192 SET LINE_STATUS = 5,
193 quantity_detailed = quantity_detailed - nvl(l_trans_qty,0),
194 secondary_quantity_detailed = secondary_quantity_detailed - nvl(l_trans_qty2,0)
195 WHERE LINE_ID = l_line_id;--:TOLINES_BLK.LINE_ID;
196 ELSE
197 UPDATE IC_TXN_REQUEST_LINES
198 SET quantity_detailed = quantity_detailed - nvl(l_trans_qty,0),
199 secondary_quantity_detailed = secondary_quantity_detailed - nvl(l_trans_qty2,0)
200 WHERE LINE_ID = l_line_id;--:TOLINES_BLK.LINE_ID;
201 END IF;
202
203
204 OPEN Get_Default_Trans(l_txn_source_line_id) ;
205 FETCH Get_Default_Trans INTO l_default_trans_id;
206 IF( Get_Default_Trans%NOTFOUND) THEN
207 CLOSE Get_Default_Trans;
208 gmi_reservation_util.println('Get_Default_Trans%NOTFOUND in Delete_Alloc_BackOrder_MO_Line');
209 RETURN FALSE;
210 END IF ;
211
212 CLOSE Get_Default_Trans;
213
214 IF NVL(l_default_trans_id,0) <> 0 THEN
215 l_in_tran_rec.trans_id := l_default_trans_id ;
216
217 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_in_tran_rec, l_out_tran_rec) THEN
218 gmi_reservation_util.println('In Delete_Alloc_BackOrder_MO_Line before call to GMI_Reservation_Util.balance_default_lot');
219 GMI_Reservation_Util.balance_default_lot(
220 p_ic_default_rec => l_out_tran_rec
221 , p_opm_item_id => l_out_tran_rec.item_id
222 , x_return_status => l_return_status
223 , x_msg_count => l_msg_count
224 , x_msg_data => l_msg_data
225 );
226
227 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
228 gmi_reservation_util.println('Error Status from GMI_Reservation_Util.balance_default_lot');
229 raise FND_API.G_EXC_ERROR;
230 END IF;
231 END IF;
232 END IF; /* IF NVL(l_default_trans_id,0) <> 0 THEN */
233
234 IF wdd_cur%ISOPEN THEN
235 CLOSE wdd_cur;
236 END IF;
237 IF get_default_trans%ISOPEN THEN
238 CLOSE get_default_trans;
239 END IF;
240 IF get_trans_id_cur%ISOPEN THEN
241 CLOSE get_trans_id_cur;
242 END IF;
243
244 gmi_reservation_util.println('Successfully Returning from Delete_Alloc_BackOrder_MO_Line');
245
246 RETURN TRUE;
247 EXCEPTION
248
249 WHEN fnd_api.g_exc_error THEN
250
251 ROLLBACK TO SAVEPOINT Delete_Alloc_BackOrder_MO_Line;
252
253 GMI_Reservation_Util.PrintLn('Exception fnd_api.g_exc_error Delete_Alloc_BackOrder_MO_Line');
254 IF wdd_cur%ISOPEN THEN
255 CLOSE wdd_cur;
256 END IF;
257 IF get_default_trans%ISOPEN THEN
258 CLOSE get_default_trans;
259 END IF;
260 IF get_trans_id_cur%ISOPEN THEN
261 CLOSE get_trans_id_cur;
262 END IF;
263
264 RETURN FALSE;
265 WHEN fnd_api.g_exc_unexpected_error THEN
266 ROLLBACK TO SAVEPOINT Delete_Alloc_BackOrder_MO_Line;
267
268 GMI_Reservation_Util.PrintLn('Exception fnd_api.g_exc_error Delete_Alloc_BackOrder_MO_Line');
269 IF wdd_cur%ISOPEN THEN
270 CLOSE wdd_cur;
271 END IF;
272 IF get_default_trans%ISOPEN THEN
273 CLOSE get_default_trans;
274 END IF;
275 IF get_trans_id_cur%ISOPEN THEN
276 CLOSE get_trans_id_cur;
277 END IF;
278
279 WHEN Others THEN
280
281 ROLLBACK TO SAVEPOINT Delete_Alloc_BackOrder_MO_Line;
282
283 GMI_Reservation_Util.PrintLn('Others Exception Delete_Alloc_BackOrder_MO_Line');
284 IF wdd_cur%ISOPEN THEN
285 CLOSE wdd_cur;
286 END IF;
287 IF get_default_trans%ISOPEN THEN
288 CLOSE get_default_trans;
289 END IF;
290 IF get_trans_id_cur%ISOPEN THEN
291 CLOSE get_trans_id_cur;
292 END IF;
293
294 RETURN FALSE;
295
296
297 END Delete_Alloc_BackOrder_MO_Line;
298
299 END GMI_OM_UTILITIES_PKG;
300
[Home] [Help]