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