[Home] [Help]
PACKAGE BODY: APPS.OE_UPDATE_LINE_ACK_UTIL
Source
1 PACKAGE BODY OE_Update_Line_Ack_Util AS
2 /* $Header: OEXULAUB.pls 120.0 2005/05/31 22:38:21 appldev noship $ */
3
4 PROCEDURE Update_Line_Ack(
5 p_request_id IN NUMBER
6 ,p_order_source_id IN NUMBER
7 ,p_orig_sys_document_ref IN VARCHAR2
8 ,p_orig_sys_line_ref IN VARCHAR2
9 ,p_orig_sys_shipment_ref IN VARCHAR2
10 ,p_change_sequence IN VARCHAR2
11 ,p_msg_count OUT NOCOPY NUMBER
12
13 ,p_msg_data OUT NOCOPY VARCHAR2
14
15 ,p_return_status OUT NOCOPY VARCHAR2
16
17 ) is
18 l_control_rec OE_Globals.Control_Rec_Type;
19
20 l_header_rec OE_Order_Pub.Header_Rec_Type;
21 l_header_rec_new OE_Order_Pub.Header_Rec_Type;
22 l_header_adj_rec OE_Order_Pub.Header_Adj_Rec_Type;
23 l_header_adj_tbl OE_Order_Pub.Header_Adj_Tbl_Type;
24 l_header_adj_tbl_new OE_Order_Pub.Header_Adj_Tbl_Type;
25 l_header_price_att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
26 l_header_price_att_tbl_new OE_Order_PUB.Header_Price_Att_Tbl_Type;
27 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
28 l_header_adj_att_tbl_new OE_Order_PUB.Header_Adj_Att_Tbl_Type;
29 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
30 l_header_adj_assoc_tbl_new OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
31 l_header_scredit_rec OE_Order_Pub.Header_Scredit_Rec_Type;
32 l_header_scredit_tbl OE_Order_Pub.Header_Scredit_Tbl_Type;
33 l_header_scredit_tbl_new OE_Order_Pub.Header_Scredit_Tbl_Type;
34
35 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type;
36 l_header_val_rec_new OE_Order_Pub.Header_Val_Rec_Type;
37 l_header_adj_val_rec OE_Order_Pub.Header_Adj_Val_Rec_Type;
38 l_header_adj_val_tbl OE_Order_Pub.Header_Adj_Val_Tbl_Type;
39 l_header_adj_val_tbl_new OE_Order_Pub.Header_Adj_Val_Tbl_Type;
40 l_header_scredit_val_rec OE_Order_Pub.Header_Scredit_Val_Rec_Type;
41 l_header_scredit_val_tbl OE_Order_Pub.Header_Scredit_Val_Tbl_Type;
42 l_header_scredit_val_tbl_new OE_Order_Pub.Header_Scredit_Val_Tbl_Type;
43
44 l_line_rec OE_Order_Pub.Line_Rec_Type;
45 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
46 l_line_tbl_new OE_Order_Pub.Line_Tbl_Type;
47 l_line_adj_rec OE_Order_Pub.Line_Adj_Rec_Type;
48 l_line_adj_tbl OE_Order_Pub.Line_Adj_Tbl_Type;
49 l_line_adj_tbl_new OE_Order_Pub.Line_Adj_Tbl_Type;
50 l_line_price_att_tbl OE_Order_Pub.Line_Price_Att_Tbl_Type;
51 l_line_price_att_tbl_new OE_Order_Pub.Line_Price_Att_Tbl_Type;
52 l_line_adj_att_tbl OE_Order_Pub.Line_Adj_Att_Tbl_Type;
53 l_line_adj_att_tbl_new OE_Order_Pub.Line_Adj_Att_Tbl_Type;
54 l_line_adj_assoc_tbl OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
55 l_line_adj_assoc_tbl_new OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
56 l_line_scredit_rec OE_Order_Pub.Line_Scredit_Rec_Type;
57 l_line_scredit_tbl OE_Order_Pub.Line_Scredit_Tbl_Type;
58 l_line_scredit_tbl_new OE_Order_Pub.Line_Scredit_Tbl_Type;
59 l_lot_serial_rec OE_Order_Pub.Lot_Serial_Rec_Type;
60 l_lot_serial_tbl OE_Order_Pub.Lot_Serial_Tbl_Type;
61 l_lot_serial_tbl_new OE_Order_Pub.Lot_Serial_Tbl_Type;
62
63 l_line_val_rec OE_Order_Pub.Line_Val_Rec_Type;
64 l_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type;
65 l_line_val_tbl_new OE_Order_Pub.Line_Val_Tbl_Type;
66 l_line_adj_val_rec OE_Order_Pub.Line_Adj_Val_Rec_Type;
67 l_line_adj_val_tbl OE_Order_Pub.Line_Adj_Val_Tbl_Type;
68 l_line_adj_val_tbl_new OE_Order_Pub.Line_Adj_Val_Tbl_Type;
69 l_line_scredit_val_rec OE_Order_Pub.Line_Scredit_Val_Rec_Type;
70 l_line_scredit_val_tbl OE_Order_Pub.Line_Scredit_Val_Tbl_Type;
71 l_line_scredit_val_tbl_new OE_Order_Pub.Line_Scredit_Val_Tbl_Type;
72 l_lot_serial_val_rec OE_Order_Pub.Lot_Serial_Val_Rec_Type;
73 l_lot_serial_val_tbl OE_Order_Pub.Lot_Serial_Val_Tbl_Type;
74 l_lot_serial_val_tbl_new OE_Order_Pub.Lot_Serial_Val_Tbl_Type;
75
76 l_action_request_rec OE_Order_Pub.Request_Rec_Type;
77 l_action_request_tbl OE_Order_Pub.Request_Tbl_Type;
78 l_action_request_tbl_new OE_Order_Pub.Request_Tbl_Type;
79
80 l_header_count NUMBER := 0;
81 l_line_count NUMBER := 0;
82 l_lot_serial_count NUMBER := 0;
83
84 l_order_source_id NUMBER := p_order_source_id;
85 l_orig_sys_document_ref VARCHAR2(50) := p_orig_sys_document_ref;
86 l_change_sequence VARCHAR2(50) := p_change_sequence;
87 l_orig_sys_line_ref VARCHAR2(50) := p_orig_sys_line_ref;
88 l_orig_sys_shipment_ref VARCHAR2(50) := p_orig_sys_shipment_ref;
89
90 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
91 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
92
93 l_msg_index NUMBER;
94
95 l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Ack';
96
97
98 /* -----------------------------------------------------------
99 Lines cursor
100 -----------------------------------------------------------
101 */
102 CURSOR l_line_cursor IS
103 SELECT order_source_id
104 , orig_sys_document_ref
105 , change_sequence
106 , orig_sys_line_ref
107 , orig_sys_shipment_ref
108 , nvl(org_id, FND_API.G_MISS_NUM)
109 , nvl(line_number, FND_API.G_MISS_NUM)
110 , nvl(shipment_number, FND_API.G_MISS_NUM)
111 , nvl(line_id, FND_API.G_MISS_NUM)
112 , nvl(line_type_id, FND_API.G_MISS_NUM)
113 , nvl(item_type_code, FND_API.G_MISS_CHAR)
114 , nvl(inventory_item_id, FND_API.G_MISS_NUM)
115 , nvl(first_ack_code, FND_API.G_MISS_CHAR)
116 , nvl(first_ack_date, FND_API.G_MISS_DATE)
117 , nvl(last_ack_code, FND_API.G_MISS_CHAR)
118 , nvl(last_ack_date, FND_API.G_MISS_DATE)
119 FROM oe_order_lines
120 WHERE order_source_id = l_order_source_id
121 AND orig_sys_document_ref = l_orig_sys_document_ref
122 AND change_sequence = l_change_sequence
123 AND orig_sys_line_ref = l_orig_sys_line_ref
124 AND orig_sys_shipment_ref = l_orig_sys_shipment_ref
125 ORDER BY orig_sys_line_ref, orig_sys_shipment_ref
126 ;
127
128 --
129 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
130 --
131 BEGIN
132 /* -----------------------------------------------------------
133 Initialization
134 -----------------------------------------------------------
135 */
136 IF l_debug_level > 0 THEN
137 oe_debug_pub.add( 'BEFORE INITIALIZATION' ) ;
138 END IF;
139
140 l_header_rec := OE_Order_Pub.G_MISS_HEADER_REC;
141 l_header_rec_new := OE_Order_Pub.G_MISS_HEADER_REC;
142 l_header_adj_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
143 l_header_adj_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
144 l_header_price_att_tbl := OE_Order_Pub.G_MISS_HEADER_PRICE_ATT_TBL;
145 l_header_price_att_tbl_new := OE_Order_Pub.G_MISS_HEADER_PRICE_ATT_TBL;
146 l_header_adj_att_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_ATT_TBL;
147 l_header_adj_att_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_ATT_TBL;
148 l_header_adj_assoc_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_ASSOC_TBL;
149 l_header_adj_assoc_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_ASSOC_TBL;
150 l_header_scredit_tbl := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
151 l_header_scredit_tbl_new := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
152
153 l_header_val_rec := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
154 l_header_val_rec_new := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
155 l_header_adj_val_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
156 l_header_adj_val_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
157 l_header_scredit_val_tbl := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
158 l_header_scredit_val_tbl_new := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
159
160 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
161 l_line_tbl := OE_Order_Pub.G_MISS_LINE_TBL;
162 l_line_tbl_new := OE_Order_Pub.G_MISS_LINE_TBL;
163 l_line_adj_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
164 l_line_adj_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
165 l_line_price_att_tbl := OE_Order_Pub.G_MISS_LINE_PRICE_ATT_TBL;
166 l_line_price_att_tbl_new := OE_Order_Pub.G_MISS_LINE_PRICE_ATT_TBL;
167 l_line_adj_att_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_ATT_TBL;
168 l_line_adj_att_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_ATT_TBL;
169 l_line_adj_assoc_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_ASSOC_TBL;
170 l_line_adj_assoc_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_ASSOC_TBL;
171 l_line_scredit_tbl := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
172 l_line_scredit_tbl_new := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
173
174 l_line_val_rec := OE_Order_Pub.G_MISS_LINE_VAL_REC;
175 l_line_val_tbl := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
176 l_line_val_tbl_new := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
177 l_line_adj_val_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
178 l_line_adj_val_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
179 l_line_scredit_val_tbl := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
180 l_line_scredit_val_tbl_new := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
181
182 l_lot_serial_rec := OE_Order_Pub.G_MISS_LOT_SERIAL_REC;
183 l_lot_serial_tbl := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
184 l_lot_serial_tbl_new := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
185
186 l_action_request_tbl := OE_Order_Pub.G_MISS_REQUEST_TBL;
187 l_action_request_tbl_new := OE_Order_Pub.G_MISS_REQUEST_TBL;
188
189 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
190 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
191
192
193 /* -----------------------------------------------------------
194 Setting Debug On
195 -----------------------------------------------------------
196 */
197 IF l_debug_level > 0 THEN
198 oe_debug_pub.add( 'BEFORE SETTING DEBUG ON' ) ;
199 END IF;
200
201 /*
202 OE_DEBUG_PUB.debug_on;
203 OE_DEBUG_PUB.SetDebugLevel(3);
204 */
205
206 /* -----------------------------------------------------------
207 Lines cursor
208 -----------------------------------------------------------
209 */
210 OPEN l_line_cursor;
211 LOOP
212 FETCH l_line_cursor
213 INTO l_line_rec.order_source_id
214 , l_line_rec.orig_sys_document_ref
215 , l_line_rec.change_sequence
216 , l_line_rec.orig_sys_line_ref
217 , l_line_rec.orig_sys_shipment_ref
218 , l_line_rec.org_id
219 , l_line_rec.line_number
220 , l_line_rec.shipment_number
221 , l_line_rec.line_id
222 , l_line_rec.line_type_id
223 , l_line_rec.item_type_code
224 , l_line_rec.inventory_item_id
225 , l_line_rec.first_ack_code
226 , l_line_rec.first_ack_date
227 , l_line_rec.last_ack_code
228 , l_line_rec.last_ack_date
229 ;
230 EXIT WHEN l_line_cursor%NOTFOUND;
231
232 l_line_count := l_line_count + 1;
233 l_line_tbl (l_line_count) := l_line_rec;
234 l_line_val_tbl(l_line_count) := l_line_val_rec;
235
236 l_orig_sys_line_ref := l_line_rec.orig_sys_line_ref;
237 l_orig_sys_shipment_ref := l_line_rec.orig_sys_shipment_ref;
238
239 END LOOP; /* Lines cursor */
240 CLOSE l_line_cursor;
241
242
243 /* -----------------------------------------------------------
244 Call Process_Order
245 -----------------------------------------------------------
246 */
247 IF l_debug_level > 0 THEN
248 oe_debug_pub.add( 'BEFORE CALLING PROCESS_ORDER' ) ;
249 END IF;
250
251 OE_Order_Grp.Process_Order(
252 p_api_version_number => 1
253 ,p_init_msg_list => l_init_msg_list
254 ,p_validation_level => l_validation_level
255 ,p_control_rec => l_control_rec
256 -- ,p_header_rec => l_header_rec
257 -- ,p_header_adj_tbl => l_header_adj_tbl
258 -- ,p_header_Scredit_tbl => l_header_scredit_tbl
259 ,p_line_tbl => l_line_tbl
260 -- ,p_line_adj_tbl => l_line_adj_tbl
261 -- ,p_line_scredit_tbl => l_line_scredit_tbl
262 -- ,p_lot_serial_tbl => l_lot_serial_tbl
263 -- ,p_action_request_tbl => l_action_request_tbl
264 ,x_header_rec => l_header_rec_new
265 ,x_header_adj_tbl => l_header_adj_tbl_new
266 ,x_header_price_att_tbl => l_header_price_att_tbl_new
267 ,x_header_adj_att_tbl => l_header_adj_att_tbl_new
268 ,x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_new
269 ,x_header_scredit_tbl => l_header_scredit_tbl_new
270 ,x_header_val_rec => l_header_val_rec_new
271 ,x_header_adj_val_tbl => l_header_adj_val_tbl_new
272 ,x_header_scredit_val_tbl => l_header_scredit_val_tbl_new
273 ,x_line_tbl => l_line_tbl_new
274 ,x_line_adj_tbl => l_line_adj_tbl_new
275 ,x_line_scredit_tbl => l_line_scredit_tbl_new
276 ,x_line_price_att_tbl => l_line_price_att_tbl_new
277 ,x_line_adj_att_tbl => l_line_adj_att_tbl_new
278 ,x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_new
279 ,x_lot_serial_tbl => l_lot_serial_tbl_new
280 ,x_line_val_tbl => l_line_val_tbl_new
281 ,x_line_adj_val_tbl => l_line_adj_val_tbl_new
282 ,x_line_scredit_val_tbl => l_line_scredit_val_tbl_new
283 ,x_lot_serial_val_tbl => l_lot_serial_val_tbl_new
284 ,x_action_request_tbl => l_action_request_tbl_new
285 ,x_msg_count => p_msg_count
286 ,x_msg_data => p_msg_data
287 ,x_return_status => p_return_status
288 );
289
290 /* -----------------------------------------------------------
291 Check Process_Order Results
292 -----------------------------------------------------------
293 */
294 IF l_debug_level > 0 THEN
295 oe_debug_pub.add( 'AFTER CALLING PROCESS_ORDER' ) ;
296 END IF;
297
298 IF l_debug_level > 0 THEN
299 oe_debug_pub.add( 'RETURN_STATUS: '||P_RETURN_STATUS ) ;
300 END IF;
301
302 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
305 RAISE FND_API.G_EXC_ERROR;
306 END IF;
307
308
309 IF l_debug_level > 0 THEN
310 oe_debug_pub.add( 'ACKNOWLEDGMENT CODE AND DATE UPDATED FOR LINE REF: '|| L_ORIG_SYS_LINE_REF ) ;
311 END IF;
312
313 /*
314 OE_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
315 p_count => p_msg_count,
316 p_data => p_msg_data);
317 */
318
319 IF p_msg_count > 0 THEN
320 FOR k IN 1 .. p_msg_count
321 LOOP
322 oe_msg_pub.get (p_msg_index => -2,
323 p_encoded => 'F',
324 p_data => p_msg_data,
325 p_msg_index_out => l_msg_index);
326
327 IF l_debug_level > 0 THEN
328 oe_debug_pub.add( 'MESSAGE: '|| P_MSG_DATA ) ;
329 END IF;
330 END LOOP;
331 END IF;
332
333
334 /* -----------------------------------------------------------
335 End of Update Line Ack
336 -----------------------------------------------------------
337 */
338 IF l_debug_level > 0 THEN
339 oe_debug_pub.add( 'END OF UPDATE LINE ACK' ) ;
340 END IF;
341
342
343 EXCEPTION
344 WHEN FND_API.G_EXC_ERROR THEN
345 p_return_status := FND_API.G_RET_STS_ERROR ;
346 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
347 p_data => p_msg_data);
348
349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
351 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
352 p_data => p_msg_data);
353
354 WHEN OTHERS THEN
355 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
356
357 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
358 then
359 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
360 end if;
361
362 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
363 p_data => p_msg_data);
364
365 END Update_Line_Ack;
366
367 END OE_Update_Line_Ack_Util;