[Home] [Help]
PACKAGE BODY: APPS.OE_ACKNOWLEDGMENT_PVT
Source
1 PACKAGE BODY OE_Acknowledgment_Pvt AS
2 /* $Header: OEXVACKB.pls 120.10.12020000.2 2013/01/21 05:22:32 kadiraju ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Acknowledgment_Pvt';
7
8 -- This to fix the performace issue with the call to the HZ tables
9 G_CURR_SOLD_TO_ORG_ID NUMBER;
10 G_CURR_ADDRESS_ID NUMBER;
11 G_TP_RET BOOLEAN := TRUE;
12 G_PRIMARY_SETUP BOOLEAN := TRUE;
13 G_POCAO_ENABLED BOOLEAN := FALSE;
14
15 /* ---------------------------------------------------------------------
16 -- Start of Comments
17 -- API name OE_Acknowledgment_Pvt
18 -- Type Private
19 -- Function
20 --
21 -- Pre-reqs
22 --
23 -- Parameters
24 --
25 -- Version Current version = 1.0
26 -- Initial version = 1.0
27 --
28 -- Notes
29 --
30 -- End of Comments
31 -- ---------------------------------------------------------------------
32 */
33
34 PROCEDURE get_address(
35 p_address_type_in IN VARCHAR2,
36 p_org_id_in IN NUMBER,
37 p_address_id_in IN NUMBER,
38 p_tp_location_code_in IN VARCHAR2,
39 p_tp_translator_code_in IN VARCHAR2,
40 l_addr1 OUT NOCOPY VARCHAR2,
41
42 l_addr2 OUT NOCOPY VARCHAR2,
43
44 l_addr3 OUT NOCOPY VARCHAR2,
45
46 l_addr4 OUT NOCOPY VARCHAR2,
47
48 l_addr_alt OUT NOCOPY VARCHAR2,
49
50 l_city OUT NOCOPY VARCHAR2,
51
52 l_county OUT NOCOPY VARCHAR2,
53
54 l_state OUT NOCOPY VARCHAR2,
55
56 l_zip OUT NOCOPY VARCHAR2,
57
58 l_province OUT NOCOPY VARCHAR2,
59
60 l_country OUT NOCOPY VARCHAR2,
61
62 l_region1 OUT NOCOPY VARCHAR2,
63
64 l_region2 OUT NOCOPY VARCHAR2,
65
66 l_region3 OUT NOCOPY VARCHAR2,
67
68 x_return_status OUT NOCOPY VARCHAR2)
69
70 IS
71
72 l_entity_id NUMBER;
73 l_msg_count NUMBER;
74 l_msg_data VARCHAR2(80);
75 l_status_code NUMBER;
76 l_return_status VARCHAR2(20);
77 l_address_type NUMBER;
78 l_org_id NUMBER;
79 l_tp_location_code VARCHAR2(3200);
80 l_tp_translator_code VARCHAR2(3200);
81 l_tp_location_name VARCHAR2(3200);
82 l_addr_id VARCHAR2(3200);
83
84 --
85 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
86 --
87 BEGIN
88 IF l_debug_level > 0 THEN
89 oe_debug_pub.add( 'ADDRESS TYPE = '||P_ADDRESS_TYPE_IN ) ;
90 END IF;
91 IF l_debug_level > 0 THEN
92 oe_debug_pub.add( 'ORG = '||P_ORG_ID_IN ) ;
93 END IF;
94 IF l_debug_level > 0 THEN
95 oe_debug_pub.add( 'ADDRESS ID = '||P_ADDRESS_ID_IN ) ;
96 END IF;
97
98 IF p_address_type_in = 'CUSTOMER' THEN
99 l_address_type := 1;
100 ELSIF p_address_type_in = 'HR_LOCATION' THEN
101 l_address_type := 2;
102 END IF;
103 IF l_debug_level > 0 THEN
104 oe_debug_pub.add( 'BEFORE CALLING EC ADDRESS DERIVATION API' ) ;
105 END IF;
106
107 ece_trading_partners_pub.ece_get_address_wrapper(
108 p_api_version_number => 1.0,
109 x_return_status => l_return_status,
110 x_msg_count => l_msg_count,
111 x_msg_data => l_msg_data,
112 x_status_code => l_status_code,
113 p_address_type => l_address_type,
114 p_transaction_type => 'POAO',
115 p_org_id_in => p_org_id_in,
116 p_address_id_in => p_address_id_in,
117 p_tp_location_code_in => p_tp_location_code_in,
118 p_translator_code_in => p_tp_translator_code_in,
119 p_tp_location_name_in => l_tp_location_name,
120 p_address_line1_in => l_addr1,
121 p_address_line2_in => l_addr2,
122 p_address_line3_in => l_addr3,
123 p_address_line4_in => l_addr4,
124 p_address_line_alt_in => l_addr_alt,
125 p_city_in => l_city,
126 p_county_in => l_county,
127 p_state_in => l_state,
128 p_zip_in => l_zip,
129 p_province_in => l_province,
130 p_country_in => l_country,
131 p_region_1_in => l_region1,
132 p_region_2_in => l_region2,
133 p_region_3_in => l_region3,
134 x_entity_id_out => l_entity_id,
135 x_org_id_out => l_org_id,
136 x_address_id_out => l_addr_id,
137 x_tp_location_code_out => l_tp_location_code,
138 x_translator_code_out => l_tp_translator_code,
139 x_tp_location_name_out => l_tp_location_name,
140 x_address_line1_out => l_addr1,
141 x_address_line2_out => l_addr2,
142 x_address_line3_out => l_addr3,
143 x_address_line4_out => l_addr4,
144 x_address_line_alt_out => l_addr_alt,
145 x_city_out => l_city,
146 x_county_out => l_county,
147 x_state_out => l_state,
148 x_zip_out => l_zip,
149 x_province_out => l_province,
150 x_country_out => l_country,
151 x_region_1_out => l_region1,
152 x_region_2_out => l_region2,
153 x_region_3_out => l_region3);
154
155
156 IF l_debug_level > 0 THEN
157 oe_debug_pub.add( 'ADDR1 = '||SUBSTR ( L_ADDR1 , 0 , 240 ) ) ;
158 END IF;
159 IF l_debug_level > 0 THEN
160 oe_debug_pub.add( 'CITY = '||L_CITY ) ;
161 END IF;
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'ZIP = '||L_ZIP ) ;
164 END IF;
165 IF l_debug_level > 0 THEN
166 oe_debug_pub.add( 'COUNTRY = '||L_COUNTRY ) ;
167 END IF;
168 EXCEPTION
169
170 WHEN OTHERS THEN
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
172
173 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
174 THEN
175 OE_MSG_PUB.Add_Exc_Msg
176 (G_PKG_NAME, 'OE_Acknowledgment_Pvt.get_address');
177 END IF;
178
179 END get_address;
180
181
182 PROCEDURE Process_Acknowledgment
183 (p_api_version_number IN NUMBER
184 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
185
186 ,p_header_rec IN OE_Order_Pub.Header_Rec_Type :=
187 OE_Order_Pub.G_MISS_HEADER_REC
188 ,p_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type
189 ,p_Header_Adj_tbl IN OE_Order_Pub.Header_Adj_Tbl_Type :=
190 OE_Order_Pub.G_MISS_HEADER_ADJ_TBL
191 ,p_Header_Adj_val_tbl IN OE_Order_Pub.Header_Adj_Val_Tbl_Type :=
192 OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL
193 ,p_Header_Scredit_tbl IN OE_Order_Pub.Header_Scredit_Tbl_Type :=
194 OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL
195 ,p_Header_Scredit_val_tbl IN OE_Order_Pub.Header_Scredit_Val_Tbl_Type :=
196 OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL
197 ,p_line_tbl IN OE_Order_Pub.Line_Tbl_Type :=
198 OE_Order_Pub.G_MISS_LINE_TBL
199 ,p_line_val_tbl IN OE_Order_Pub.Line_Val_Tbl_Type
200 ,p_Line_Adj_tbl IN OE_Order_Pub.Line_Adj_Tbl_Type :=
201 OE_Order_Pub.G_MISS_LINE_ADJ_TBL
202 ,p_Line_Adj_val_tbl IN OE_Order_Pub.Line_Adj_Val_Tbl_Type :=
203 OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL
204 ,p_Line_Scredit_tbl IN OE_Order_Pub.Line_Scredit_Tbl_Type :=
205 OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL
206 ,p_Line_Scredit_val_tbl IN OE_Order_Pub.Line_Scredit_Val_Tbl_Type :=
207 OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL
208 ,p_Lot_Serial_tbl IN OE_Order_Pub.Lot_Serial_Tbl_Type :=
209 OE_Order_Pub.G_MISS_LOT_SERIAL_TBL
210 ,p_Lot_Serial_val_tbl IN OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
211 OE_Order_Pub.G_MISS_LOT_SERIAL_VAL_TBL
212 ,p_action_request_tbl IN OE_Order_Pub.Request_Tbl_Type :=
213 OE_Order_Pub.G_MISS_REQUEST_TBL
214 ,p_old_header_rec IN OE_Order_Pub.Header_Rec_Type :=
215 OE_Order_Pub.G_MISS_HEADER_REC
216 ,p_old_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type :=
217 OE_Order_Pub.G_MISS_HEADER_VAL_REC
218 ,p_old_Header_Adj_tbl IN OE_Order_Pub.Header_Adj_Tbl_Type :=
219 OE_Order_Pub.G_MISS_HEADER_ADJ_TBL
220 ,p_old_Header_Adj_val_tbl IN OE_Order_Pub.Header_Adj_Val_Tbl_Type :=
221 OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL
222 ,p_old_Header_Scredit_tbl IN OE_Order_Pub.Header_Scredit_Tbl_Type :=
223 OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL
224 ,p_old_Header_Scredit_val_tbl IN OE_Order_Pub.Header_Scredit_Val_Tbl_Type :=
225 OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL
226 ,p_old_line_tbl IN OE_Order_Pub.Line_Tbl_Type :=
227 OE_Order_Pub.G_MISS_LINE_TBL
228 ,p_old_line_val_tbl IN OE_Order_Pub.Line_Val_Tbl_Type :=
229 OE_Order_Pub.G_MISS_LINE_VAL_TBL
230 ,p_old_Line_Adj_tbl IN OE_Order_Pub.Line_Adj_Tbl_Type :=
231 OE_Order_Pub.G_MISS_LINE_ADJ_TBL
232 ,p_old_Line_Adj_val_tbl IN OE_Order_Pub.Line_Adj_Val_Tbl_Type :=
233 OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL
234 ,p_old_Line_Scredit_tbl IN OE_Order_Pub.Line_Scredit_Tbl_Type :=
235 OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL
236 ,p_old_Line_Scredit_val_tbl IN OE_Order_Pub.Line_Scredit_Val_Tbl_Type :=
237 OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL
238 ,p_old_Lot_Serial_tbl IN OE_Order_Pub.Lot_Serial_Tbl_Type :=
239 OE_Order_Pub.G_MISS_LOT_SERIAL_TBL
240 ,p_old_Lot_Serial_val_tbl IN OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
241 OE_Order_Pub.G_MISS_LOT_SERIAL_VAL_TBL
242
243 ,p_buyer_seller_flag IN VARCHAR2 DEFAULT 'B'
244 ,p_reject_order IN VARCHAR2 DEFAULT 'N'
245
246 ,x_return_status OUT NOCOPY VARCHAR2
247
248 )
249 IS
250 l_api_version_number CONSTANT NUMBER := 1.0;
251 l_api_name CONSTANT VARCHAR2(30):= 'Process_Acknowledgment';
252 l_return_status VARCHAR2(1) := FND_API.G_MISS_CHAR;
253
254 l_header_id NUMBER;
255
256 l_header_rec OE_Order_Pub.Header_Rec_Type :=
257 p_header_rec;
258 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type :=
259 p_header_val_rec;
260 l_Header_Adj_tbl OE_Order_Pub.Header_Adj_Tbl_Type :=
261 p_Header_Adj_tbl;
262 l_Header_Adj_val_tbl OE_Order_Pub.Header_Adj_Val_Tbl_Type :=
263 p_Header_Adj_val_tbl;
264 l_Header_Price_Att_tbl OE_Order_Pub.Header_Price_Att_Tbl_Type;
265 l_Header_Adj_Att_tbl OE_Order_Pub.Header_Adj_Att_Tbl_Type;
266 l_Header_Adj_Assoc_tbl OE_Order_Pub.Header_Adj_Assoc_Tbl_Type;
267 l_Header_Scredit_tbl OE_Order_Pub.Header_Scredit_Tbl_Type :=
268 p_Header_Scredit_tbl;
269 l_Header_Scredit_val_tbl OE_Order_Pub.Header_Scredit_Val_Tbl_Type :=
270 p_Header_Scredit_val_tbl;
271 l_line_rec OE_Order_Pub.Line_Rec_Type ;
272 l_line_tbl OE_Order_Pub.Line_Tbl_Type :=
273 p_line_tbl;
274 l_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type :=
275 p_line_val_tbl;
276 l_Line_Adj_tbl OE_Order_Pub.Line_Adj_Tbl_Type :=
277 p_Line_Adj_tbl;
278 l_Line_Adj_val_tbl OE_Order_Pub.Line_Adj_Val_Tbl_Type :=
279 p_Line_Adj_val_tbl;
280 l_Line_Price_Att_tbl OE_Order_Pub.Line_Price_Att_Tbl_Type;
281 l_Line_Adj_Att_tbl OE_Order_Pub.Line_Adj_Att_Tbl_Type;
282 l_Line_Adj_Assoc_tbl OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
283 l_Line_Scredit_tbl OE_Order_Pub.Line_Scredit_Tbl_Type :=
284 p_Line_Scredit_tbl;
285 l_Line_Scredit_val_tbl OE_Order_Pub.Line_Scredit_Val_Tbl_Type :=
286 p_Line_Scredit_val_tbl;
287 l_Lot_Serial_tbl OE_Order_Pub.Lot_Serial_Tbl_Type :=
288 p_Lot_Serial_tbl;
289 l_Lot_Serial_val_tbl OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
290 p_Lot_Serial_val_tbl;
291 l_action_request_tbl OE_Order_Pub.Request_Tbl_Type :=
292 p_action_request_tbl;
293
294 l_old_header_rec OE_Order_Pub.Header_Rec_Type :=
295 p_old_header_rec;
296 l_old_header_val_rec OE_Order_Pub.Header_Val_Rec_Type :=
297 p_old_header_val_rec;
298 l_old_Header_Adj_tbl OE_Order_Pub.Header_Adj_Tbl_Type :=
299 p_old_Header_Adj_tbl;
300 l_old_Header_Adj_val_tbl OE_Order_Pub.Header_Adj_Val_Tbl_Type :=
301 p_old_Header_Adj_val_tbl;
302 l_old_Header_Price_Att_tbl OE_Order_Pub.Header_Price_Att_Tbl_Type;
303 l_old_Header_Adj_Att_tbl OE_Order_Pub.Header_Adj_Att_Tbl_Type;
304 l_old_Header_Adj_Assoc_tbl OE_Order_Pub.Header_Adj_Assoc_Tbl_Type;
305 l_old_Header_Scredit_tbl OE_Order_Pub.Header_Scredit_Tbl_Type :=
306 p_old_Header_Scredit_tbl;
307 l_old_Header_Scredit_val_tbl OE_Order_Pub.Header_Scredit_Val_Tbl_Type :=
308 p_old_Header_Scredit_val_tbl;
309 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type :=
310 p_old_line_tbl;
311 l_old_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type :=
312 p_old_line_val_tbl;
313 l_old_Line_Adj_tbl OE_Order_Pub.Line_Adj_Tbl_Type :=
314 p_old_Line_Adj_tbl;
315 l_old_Line_Adj_val_tbl OE_Order_Pub.Line_Adj_Val_Tbl_Type :=
316 p_old_Line_Adj_val_tbl;
317 l_old_Line_Price_Att_tbl OE_Order_Pub.Line_Price_Att_Tbl_Type;
318 l_old_Line_Adj_Att_tbl OE_Order_Pub.Line_Adj_Att_Tbl_Type;
319 l_old_Line_Adj_Assoc_tbl OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
320 l_old_Line_Scredit_tbl OE_Order_Pub.Line_Scredit_Tbl_Type :=
321 p_old_Line_Scredit_tbl;
322 l_old_Line_Scredit_val_tbl OE_Order_Pub.Line_Scredit_Val_Tbl_Type :=
323 p_old_Line_Scredit_val_tbl;
324 l_old_Lot_Serial_tbl OE_Order_Pub.Lot_Serial_Tbl_Type :=
325 p_old_Lot_Serial_tbl;
326 l_old_Lot_Serial_val_tbl OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
327 p_old_Lot_Serial_val_tbl;
328
329 l_address_id NUMBER;
330 l_tp_ret BOOLEAN;
331 l_tp_ret_status VARCHAR2(200);
332 l_msg_count NUMBER;
333 l_msg_data VARCHAR2(200);
334
335 -- Variable to store return value for Trading Partner
336 l_trading_partner VARCHAR2(1) := 'Y';
337
338 -- Remove below flag once it is passed as parameter
339 l_force_ack VARCHAR2(1) := FND_API.G_MISS_CHAR;
340
341 l_ack_req_flag VARCHAR2(1) := 'N';
342 l_booked_shipped VARCHAR2(1) := 'Y';
343
344 -- Remove below flag once it is passed as parameter
345 l_rejected_lines VARCHAR2(1) := FND_API.G_MISS_CHAR;
346
347 -- Following Local Variables are for getting the data related to
348 -- Rejected Lines and Corresponding Lotserial for Acknowledgment
349 l_reject_line_tbl OE_Order_Pub.Line_Tbl_Type :=
350 OE_Order_Pub.G_MISS_LINE_TBL;
351 l_reject_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type :=
352 OE_Order_Pub.G_MISS_LINE_VAL_TBL;
353 l_reject_Lot_Serial_tbl OE_Order_Pub.Lot_Serial_Tbl_Type :=
354 OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
355 l_reject_Lot_Serial_val_tbl OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
356 OE_Order_Pub.G_MISS_LOT_SERIAL_VAL_TBL;
357
358 -- This variable will be passed as value for parameter p_reject_order
359 -- In Insert_Row APIs for creating new rows for the rejected lines
360 -- in Acknowledgment Line and Lotserial tables.
361 -- The Value will be 'L' for creating records in acknowledgment
362 -- tables other value will be directly passed from Import Order API
363
364 l_create_rejects VARCHAR2(1) := 'L';
365
366 l_booked_flag VARCHAR2(1) := '';
367
368 i pls_integer;
369 j pls_integer;
370 l_line_index pls_integer;
371 -- This variable is passed to EDI's check for trading partner
372 l_site_use_id NUMBER;
373
374 -- Variable for checking the install staus of the EC product before doing
375 -- Any processing. Short term fix for the issue 1633094
376 l_status varchar2(1);
377 l_industry varchar2(1);
378 l_o_schema varchar2(30);
379 l_validation_org_id NUMBER;
380 l_concatenated_segments VARCHAR2(240); --For bug4309609
381
382 l_addr1 VARCHAR2(3200) := NULL;
383 l_addr2 VARCHAR2(3200) := NULL;
384 l_addr3 VARCHAR2(3200) := NULL;
385 l_addr4 VARCHAR2(3200) := NULL;
386 l_addr_alt VARCHAR2(3200) := NULL;
387 l_city VARCHAR2(3200) := NULL;
388 l_county VARCHAR2(3200) := NULL;
389 l_state VARCHAR2(3200) := NULL;
390 l_zip VARCHAR2(3200) := NULL;
391 l_province VARCHAR2(3200) := NULL;
392 l_country VARCHAR2(3200) := NULL;
393 l_region1 VARCHAR2(3200) := NULL;
394 l_region2 VARCHAR2(3200) := NULL;
395 l_region3 VARCHAR2(3200) := NULL;
396 l_bill_to_addr_id NUMBER;
397 l_ship_to_addr_id NUMBER;
398 l_sold_to_addr_id NUMBER;
399 l_ship_from_addr_id NUMBER;
400 l_bill_to_addr_code VARCHAR2(40);
401 l_ship_to_addr_code VARCHAR2(40);
402 l_sold_to_addr_code VARCHAR2(40);
403 l_ship_from_addr_code VARCHAR2(40);
404 l_sold_to_location_code VARCHAR2(40);
405 l_ship_from_location_code VARCHAR2(40);
406 l_ship_to_location_code VARCHAR2(40);
407 l_bill_to_location_code VARCHAR2(40);
408 /* This code was added for Blanket Sales Order */
409 l_top_model_line_id number;
410 l_blanket_number number;
411 l_blanket_line_number number;
412 CURSOR get_top_model(p_top_model_line_id in number) IS
413 SELECT
414 blanket_number,
415 blanket_line_number
416 FROM
417 OE_ORDER_LINES_ALL
418 WHERE
419 top_model_line_id = p_top_model_line_id;
420 --
421 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
422 --
423
424 l_header_rec_isnull Varchar2(1) := 'N';
425
426 -- bug 3439319
427 l_sales_order_id Number;
428
429 BEGIN
430
431 IF l_debug_level > 0 THEN
432 oe_debug_pub.add( 'ENTERING OE_ACKNOWLEDGMENT_PVT.PROCESS_ACKNOWLEDGMENT' , 1 ) ;
433 oe_debug_pub.add( 'ENTERING OE_ACKNOWLEDGMENT_PVT.PROCESS_ACKNOWLEDGMENT', 0.5); -- debug level changed to 0.5 for bug 13435459
434 END IF;
435
436 /***
437 -- Bug fix 1633094 start
438 oe_debug_pub.add('Before Calling FND api to check the EC inst',1);
439 IF fnd_installation.get_app_info('EC', l_status, l_industry, l_o_schema)
440 THEN
441 IF nvl(l_status,'N') = 'N' THEN
442 oe_debug_pub.add('EC not installed - No ACK required',1);
443 oe_debug_pub.add('Exiting OE_Acknowledgment_Pvt.Process_acknowledgment',1);
444 x_return_status := FND_API.G_RET_STS_SUCCESS;
445 RETURN;
446 END IF; -- l_status is N
447 ELSE
448 oe_debug_pub.add('Call to fnd_installation.get_app_info is FALSE',1);
449 oe_debug_pub.add('Exiting OE_Acknowledgment_Pvt.Process_acknowledgment',1);
450 x_return_status := FND_API.G_RET_STS_SUCCESS;
451 RETURN;
452 END IF; -- call to fnd_installation
453 -- Bug fix 1633094 end
454 ***/
455
456 -- lkxu, for bug 1701377
457 -- to check if EC is installed
458 IF OE_GLOBALS.G_EC_INSTALLED IS NULL THEN
459 OE_GLOBALS.G_EC_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(175);
460 END IF;
461
462 IF OE_GLOBALS.G_EC_INSTALLED <> 'Y' THEN
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add( 'EC NOT INSTALLED - NO ACK REQUIRED' , 1 ) ;
465 END IF;
466 IF l_debug_level > 0 THEN
467 oe_debug_pub.add( 'EXITING OE_ACKNOWLEDGMENT_PVT.PROCESS_ACKNOWLEDGMENT' , 1 ) ;
468 END IF;
469 RETURN;
470 END IF;
471
472 IF l_debug_level > 0 THEN
473 oe_debug_pub.add( 'BEFORE CHECKING IF HEADER_ID EXISTS FOR HEADER REC' , 3 ) ;
474 END IF;
475 IF l_debug_level > 0 THEN
476 oe_debug_pub.add( 'HEADER_ID_ACK = '||L_HEADER_REC.HEADER_ID ) ;
477 END IF;
478
479 IF (l_header_rec.header_id <> FND_API.G_MISS_NUM AND
480 nvl(l_header_rec.header_id,0) <> 0)
481 THEN
482 IF l_debug_level > 0 THEN
483 oe_debug_pub.add( 'BEFORE GETTING BOOKED_FLAG' , 3 ) ;
484 END IF;
485
486 -- l_header_rec := OE_Header_Util.Query_Row
487 -- (p_header_id => l_header_rec.header_id);
488
489 IF l_debug_level > 0 THEN
490 oe_debug_pub.add( 'l_header_rec: price list '||l_header_val_rec.price_list ) ;
491 oe_debug_pub.add( 'L_header_rec: order_category_code '||l_header_rec.order_category_code ) ;
492 oe_debug_pub.add( 'l_header_rec: first_ack date '||l_header_rec.first_ack_date ) ;
493 oe_debug_pub.add( 'l_header_rec: first_ack code '||l_header_rec.first_ack_code ) ;
494 oe_debug_pub.add( 'l_header_rec: shipping instructions' ||l_header_rec.shipping_instructions ) ;
495 oe_debug_pub.add( 'l_header_rec: packing instructions' ||l_header_rec.packing_instructions ) ;
496 END IF;
497 BEGIN
498 SELECT booked_flag INTO l_booked_flag
499 FROM oe_order_headers
500 WHERE header_id = l_header_rec.header_id;
501
502 IF l_booked_flag = 'Y' THEN
503 l_header_rec.booked_flag := l_booked_flag;
504 IF l_debug_level > 0 THEN
505 oe_debug_pub.add( 'ORDER IS BOOKED 1' ) ;
506 END IF;
507 END IF;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512 IF l_debug_level > 0 THEN
513 oe_debug_pub.add( 'EXCEPTION IN GETTING BOOKED_FLAG' , 3 ) ;
514 END IF;
515 RETURN;
516 END;
517
518 ELSIF l_line_tbl.count > 0 AND
519 (l_header_rec.header_id = FND_API.G_MISS_NUM OR
520 nvl(l_header_rec.header_id,0) = 0) AND
521 (l_line_tbl(l_line_tbl.first).header_id <> FND_API.G_MISS_NUM AND
522 nvl(l_line_tbl(l_line_tbl.first).header_id,0) <> 0)
523 THEN
524 Begin
525 IF l_debug_level > 0 THEN
526 oe_debug_pub.add( 'BEFORE QUERYING THE HEADER REC' ) ;
527 END IF;
528 -- l_header_rec := OE_Header_Util.Query_Row
529 -- (p_header_id => l_line_tbl(1).header_id);
530 -- This is to get the index of the line table, as
531 -- the global change, create index, which can be something
532 -- other than normal sequence of 1...so on.
533 l_line_index := l_line_tbl.First;
534 -- start bug 4048709, if the global picture header record is not present
535 -- that means that there were no header level changes
536 -- but we still need the header record to perform tp check + send ack,
537 -- so get it from the cache if possible
538 IF l_debug_level > 0 THEN
539 oe_debug_pub.add( 'Header Id in cache is : '||oe_order_cache.g_header_rec.header_id);
540 END IF;
541 IF OE_ORDER_CACHE.g_header_rec.header_id <> FND_API.G_MISS_NUM AND
542 nvl(OE_ORDER_CACHE.g_header_rec.header_id,0) = l_line_tbl(l_line_index).header_id THEN
543 l_header_rec := OE_ORDER_CACHE.g_header_rec;
544 IF l_debug_level > 0 THEN
545 oe_debug_pub.add('Assigned header record from cache with booked flag '|| l_header_rec.booked_flag);
546 END IF;
547 ELSE
548 -- end bug 4048709
549 OE_Header_Util.Query_Row
550 -- Below l_line_index is passed insted of 1 for post H
551 (p_header_id => l_line_tbl(l_line_index).header_id,
552 x_header_rec => l_header_rec);
553 END IF ;
554 --bug 3592147
555 l_header_rec_isnull := 'Y';
556 Exception
557 When Others Then
558 x_return_status := FND_API.G_RET_STS_SUCCESS;
559 IF l_debug_level > 0 THEN
560 oe_debug_pub.add( 'NOTHING TO ACKNOWLEDGE - ELSIF' , 3 ) ;
561 END IF;
562 RETURN;
563 End;
564 ELSE
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566 IF l_debug_level > 0 THEN
567 oe_debug_pub.add( 'NOTHING TO ACKNOWLEDGE - ELSE' , 3 ) ;
568 END IF;
569 RETURN;
570 END IF;
571
572
573 /* -------------------------------------------------------------
574 -- Following API is called to check if the Customer is a
575 -- Trading Partner or not? Acknowledgment will only be sent
576 -- to those customers who are trading partners and who
577 -- have enabled the acknowledgment transactions.
578 -- -------------------------------------------------------------
579 */
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add( 'BEFORE CHECKING IF TRADING PARTNER' ) ;
582 END IF;
583
584 -- aksingh performance cache
585 IF G_CURR_SOLD_TO_ORG_ID = l_header_rec.sold_to_org_id AND
586 (G_TP_RET = FALSE OR
587 G_PRIMARY_SETUP = FALSE)
588 THEN
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590 IF l_debug_level > 0 THEN
591 oe_debug_pub.add( 'CHECKED CACHED VALUE POAO NOT EDI ENABLED G_CURR' , 3 ) ;
592 END IF;
593 RETURN;
594 END IF;
595
596 -- aksingh performance cache
597 IF G_CURR_SOLD_TO_ORG_ID = l_header_rec.sold_to_org_id AND
598 G_CURR_ADDRESS_ID IS NOT NULL AND
599 G_TP_RET = TRUE
600 THEN
601 -- No need to check anything all information is available
602 -- Go and Create acknowledgment records
603 goto create_ack;
604 END IF;
605
606 l_tp_ret := FALSE;
607
608 IF l_debug_level > 0 THEN
609 oe_debug_pub.add( 'SOLD_TO_ORG_ID: '||TO_CHAR ( L_HEADER_REC.SOLD_TO_ORG_ID ) , 3 ) ;
610 END IF;
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add( 'INVOICE_TO_ORG_ID: '||TO_CHAR ( L_HEADER_REC.INVOICE_TO_ORG_ID ) , 3 ) ;
613 END IF;
614
615 BEGIN
616 IF l_debug_level > 0 THEN
617 oe_debug_pub.add( 'BEFORE GETTING SOLD_TO SITE FOR THE CUSTOMER' ) ;
618 END IF;
619 -----------------------------------------------------------------
620 -- Fixing bug #1513426, using the HZ tables directly instead of
621 -- RA views to improve the performance
622 ------------------------------------------------------------------
623
624 -- This sql is only performed when customer change to improve performance
625
626
627 SELECT /*MOAC_SQL_CHANGES*/ b.site_use_id, a.cust_acct_site_id
628 INTO l_site_use_id, l_address_id
629 FROM hz_cust_site_uses b, hz_cust_acct_sites_all a
630 WHERE a.cust_acct_site_id = b.cust_acct_site_id
631 AND a.cust_account_id = l_header_rec.sold_to_org_id
632 AND b.site_use_code = 'SOLD_TO'
633 AND b.primary_flag = 'Y'
634 AND b.status = 'A'
635 AND b.org_id=a.org_id
636 AND a.status = 'A';
637
638 G_CURR_SOLD_TO_ORG_ID := l_header_rec.sold_to_org_id;
639 G_CURR_ADDRESS_ID := l_address_id;
640 G_PRIMARY_SETUP := TRUE;
641 IF l_debug_level > 0 THEN
642 oe_debug_pub.add( 'L_SITE_USE_ID ' || TO_CHAR ( L_SITE_USE_ID ) , 3 ) ;
643 END IF;
644 IF l_debug_level > 0 THEN
645 oe_debug_pub.add( 'L_ADDRESS_ID ' || TO_CHAR ( L_ADDRESS_ID ) , 3 ) ;
646 END IF;
647 IF l_debug_level > 0 THEN
648 oe_debug_pub.add( 'AFTER GETTING SOLD_TO SITE FOR THE CUSTOMER' ) ;
649 END IF;
650 EXCEPTION
651
652 WHEN NO_DATA_FOUND THEN
653 x_return_status := FND_API.G_RET_STS_SUCCESS;
654 IF l_debug_level > 0 THEN
655 oe_debug_pub.add( 'NO PRIMARY SOLD_TO SET FOR CUSTOMER' , 2 ) ;
656 END IF;
657 G_CURR_SOLD_TO_ORG_ID := l_header_rec.sold_to_org_id;
658 G_CURR_ADDRESS_ID := NULL;
659 G_PRIMARY_SETUP := FALSE;
660 RETURN;
661 WHEN OTHERS THEN
662
663 x_return_status := FND_API.G_RET_STS_SUCCESS;
664 IF l_debug_level > 0 THEN
665 oe_debug_pub.add( 'NOT ABLE TO GET PRIMARY SOLD_TO FOR CUSTOMER' , 1 ) ;
666 END IF;
667 RETURN;
668 END;
669
670
671 l_tp_ret := EC_TRADING_PARTNER_PVT.Is_Entity_Enabled (
672 p_api_version_number => 1.0
673 ,p_init_msg_list => null
674 ,p_simulate => null
675 ,p_commit => null
676 ,p_validation_level => null
677 ,p_transaction_type => 'POAO'
678 ,p_transaction_subtype => null
679 ,p_entity_type => EC_TRADING_PARTNER_PVT.G_CUSTOMER
680 ,p_entity_id => l_address_id
681 ,p_return_status => l_tp_ret_status
682 ,p_msg_count => l_msg_count
683 ,p_msg_data => l_msg_data);
684
685 IF l_header_rec.first_ack_code is not null THEN
686 IF l_debug_level > 0 THEN
687 oe_debug_pub.add( 'Check if POCAO Enabled ',1 );
688 END IF;
689
690 G_POCAO_ENABLED := EC_TRADING_PARTNER_PVT.Is_Entity_Enabled (
691 p_api_version_number => 1.0
692 ,p_init_msg_list => null
693 ,p_simulate => null
694 ,p_commit => null
695 ,p_validation_level => null
696 ,p_transaction_type => 'POCAO'
697 ,p_transaction_subtype => null
698 ,p_entity_type => EC_TRADING_PARTNER_PVT.G_CUSTOMER
699 ,p_entity_id => l_address_id
700 ,p_return_status => l_tp_ret_status
701 ,p_msg_count => l_msg_count
702 ,p_msg_data => l_msg_data);
703
704 IF NOT G_POCAO_ENABLED THEN
705 IF l_debug_level > 0 THEN
706 oe_debug_pub.add( 'POCAO NOT ENABLED',1 ) ;
707 END IF;
708 l_tp_ret := FALSE;
709 END IF;
710 END IF;
711
712
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add( 'AFTER CALL TO THE EDI API' ) ;
715 END IF;
716
717 IF l_tp_ret = FALSE then
718 G_TP_RET := FALSE;
719 x_return_status := FND_API.G_RET_STS_SUCCESS;
720 IF l_debug_level > 0 THEN
721 oe_debug_pub.add( 'CUSTOMER/INVOICE-TO-ADDRESS/POAO NOT EDI ENABLED' , 3 ) ;
722 END IF;
723 RETURN;
724 END IF;
725
726 G_TP_RET := TRUE;
727
728 <<create_ack>>
729 /* --------------------------------------------------------------------
730 -- If Force_Ack flag is not 'Y' then Check if the Booked_flag = 'Y'
731 -- and Schedule Ship Date is not null else this check is not required.
732 -- --------------------------------------------------------------------
733 */
734 l_booked_shipped := 'Y';
735 /*
736 OE_Line_Util.Query_Rows
737 ( p_header_id => l_header_rec.header_id
738 , x_line_tbl => l_line_tbl
739 );
740 */
741 -- l_header_rec.booked_flag := 'Y'; -- Temp, because of a bug in PO
742
743 IF nvl(l_force_ack, 'Y') <> 'Y' THEN
744 IF l_header_rec.booked_flag = 'Y' THEN
745 /* commented out nocopy as part of post H global change
746
747 i := l_line_tbl.First;
748 While i is not null loop
749 --FOR I IN 1..l_line_tbl.COUNT
750
751 IF l_line_tbl(I).schedule_ship_date is not null AND
752 l_line_tbl(I).schedule_arrival_date is not null
753 THEN
754 -- aksingh 2 line added on 10/22/00
755 NULL;
756 ELSE
757 oe_debug_pub.add('lines are not scheduled',3);
758 l_booked_shipped := 'N';
759 EXIT;
760 END IF;
761 i := l_line_tbl.Next(i);
762 END LOOP;
763 */ -- Till this commented for the post H
764 NULL;
765 ELSE
766 IF l_debug_level > 0 THEN
767 oe_debug_pub.add( 'ORDER IS NOT BOOKED' , 3 ) ;
768 END IF;
769 l_booked_shipped := 'N';
770 END IF;
771 END IF;
772
773 IF l_booked_shipped = 'N' THEN
774 x_return_status := FND_API.G_RET_STS_SUCCESS;
775 IF l_debug_level > 0 THEN
776 oe_debug_pub.add( 'ACKNOWLEDGMENT NOT REQUIRED' , 3 ) ;
777 END IF;
778 RETURN;
779 END IF;
780
781 IF l_debug_level > 0 THEN
782 oe_debug_pub.add( 'BEFORE CHECKING IF FIELDS CHANGED' , 3 ) ;
783 END IF;
784
785 /* -------------------------------------------------------------
786 Now Check to see at what level the data has been changed
787 If only Header Level then send only Header Data for acknowledgment
788 Else If Shipment Level then send both Header and Line level data
789 -------------------------------------------------------------
790 */
791 J:= l_line_tbl.last;
792 I:= l_line_tbl.first;
793 IF l_debug_level > 0 THEN
794 oe_debug_pub.add( 'I = '||I ) ;
795 END IF;
796 IF l_debug_level > 0 THEN
797 oe_debug_pub.add( 'J = '||J ) ;
798 oe_debug_pub.add('header first_ack_code: => ' || l_header_rec.first_ack_code);
799 END IF;
800
801
802 While i is not null loop
803 /* Bug 2416561 : Calling the Convert_Miss_To_Null for the id records */
804 l_line_rec := l_line_tbl(I);
805 oe_line_util.Convert_Miss_To_Null(p_x_line_rec =>l_line_rec);
806 l_line_tbl(I) := l_line_rec;
807
808 -- bug 3439319 added this block instead of NULLing out the resv_qty
809 Begin
810 IF l_debug_level > 0 THEN
811 oe_debug_pub.add('header_id => ' || l_line_tbl(I).header_id);
812 oe_debug_pub.add('line_id => ' || l_line_tbl(I).line_id);
813 oe_debug_pub.add('org_id => ' || l_line_tbl(I).org_id);
814 oe_debug_pub.add('unit_selling_price = '||l_line_tbl(I).unit_selling_price);
815 oe_debug_pub.add('unit_selling_price old = '||l_old_line_tbl(I).unit_selling_price);
816 oe_debug_pub.add('schedule_ship_date = '||l_line_tbl(I).schedule_ship_date);
817 oe_debug_pub.add('schedule_ship_date old = '||l_old_line_tbl(I).schedule_ship_date);
818 oe_debug_pub.add('ordered_quantity = '||l_line_tbl(I).ordered_quantity);
819 oe_debug_pub.add('ordered_quantity_old = '||l_old_line_tbl(I).ordered_quantity);
820 oe_debug_pub.add('schedule_arrival_date = '||l_line_tbl(I).schedule_arrival_date);
821 oe_debug_pub.add('schedule_arrival_date old = '||l_old_line_tbl(I).schedule_arrival_date);
822 oe_debug_pub.add('shipped_quantity = '||nvl(l_line_tbl(I).shipped_quantity,0));
823 oe_debug_pub.add('shipped_quantity old = '||nvl(l_old_line_tbl(I).shipped_quantity,0));
824 oe_debug_pub.add('first_ack_code line = '||nvl(l_line_tbl(I).first_ack_code,'Nul'));
825 oe_debug_pub.add('uom = '||l_line_tbl(I).order_quantity_uom);
826 oe_debug_pub.add('uom old = '||l_old_line_tbl(I).order_quantity_uom);
827 oe_debug_pub.add('inv item id = '||l_line_tbl(I).inventory_item_id);
828 oe_debug_pub.add('inv item id old = '||l_old_line_tbl(I).inventory_item_id);
829 oe_debug_pub.add('line first_ack_code old: => ' || l_old_line_tbl(I).first_ack_code);
830 oe_debug_pub.add('line first_ack_code new: => ' || l_line_tbl(I).first_ack_code);
831 oe_debug_pub.add('line operation old: => ' || l_old_line_tbl(I).operation);
832 oe_debug_pub.add('line operation new: => ' || l_line_tbl(I).operation);
833 END IF;
834
835 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_tbl(I).header_id);
836
837 IF l_debug_level > 0 THEN
838 oe_debug_pub.add('l_sales_order_id => ' || l_sales_order_id);
839 END IF;
840
841 l_line_tbl(I).reserved_quantity := oe_line_util.Get_Reserved_Quantity (
842 p_header_id => l_sales_order_id,
843 p_line_id => p_line_tbl(I).line_id,
844 p_org_id => p_line_tbl(I).org_id);
845 IF l_debug_level > 0 THEN
846 oe_debug_pub.add('Reserved_Qty => ' || l_line_tbl(I).reserved_quantity);
847 END IF;
848 Exception
849 When Others Then
850 oe_debug_pub.add('When Others After the call to get_reserv_qty in OEXVACKB');
851 l_line_tbl(I).reserved_quantity := NULL;
852 END;
853
854 -- FOR I IN l_line_tbl.first..l_line_tbl.last LOOP
855
856 -- modified the following condition to fix 2380911 to take care of INSERT
857 -- operation if the Acknowledgment is already extracted.
858 IF NOT (OE_GLOBALS.Equal(l_line_tbl(I).inventory_item_id,
859 l_old_line_tbl(I).inventory_item_id)
860 AND OE_GLOBALS.Equal(l_line_tbl(I).unit_selling_price,
861 l_old_line_tbl(I).unit_selling_price)
862 AND OE_GLOBALS.Equal(l_line_tbl(I).ordered_quantity,
863 l_old_line_tbl(I).ordered_quantity)
864 AND OE_GLOBALS.Equal(l_line_tbl(I).order_quantity_uom,
865 l_old_line_tbl(I).order_quantity_uom)
866 AND OE_GLOBALS.Equal(l_line_tbl(I).shipped_quantity,
867 l_old_line_tbl(I).shipped_quantity)
868 AND OE_GLOBALS.Equal(l_line_tbl(I).schedule_ship_date,
869 l_old_line_tbl(I).schedule_ship_date)
870 AND OE_GLOBALS.Equal(l_line_tbl(I).schedule_arrival_date,
871 l_old_line_tbl(I).schedule_arrival_date)
872 ) OR (
873 l_old_line_tbl(I).operation = Oe_Globals.G_OPR_INSERT OR
874 l_old_line_tbl(I).operation = Oe_Globals.G_OPR_CREATE
875 )
876 OR ( l_line_tbl(I).first_ack_code is null AND
877 l_header_rec.first_ack_code is not null )--bug7207426
878 THEN
879 -- Set local variable to continue Acknowledgment processing
880 -- And Exit from Loop
881 IF l_debug_level > 0 THEN
882 oe_debug_pub.add( 'LINE DATA HAS CHANGED' ) ;
883 END IF;
884
885 l_ack_req_flag := 'B';
886 /* Bug 2671184 :
887 IF POCAO and a new line is added to the Order */
888 If l_header_rec.first_ack_code is not null AND
889 l_line_tbl(I).FIRST_ACK_CODE is null
890 THEN
891 l_line_tbl(I).FIRST_ACK_CODE := 'DR';
892 l_line_tbl(I).FIRST_ACK_DATE := sysdate;
893 l_old_line_tbl(I).FIRST_ACK_DATE := sysdate;
894 end if;
895
896 ELSE
897 -- no attribute change was detected so if the acknowledgment has been
898 -- extracted previously, do not send this line
899 IF l_debug_level > 0 THEN
900 oe_debug_pub.add('No attribute change detected.');
901 END IF;
902
903 If l_header_rec.first_ack_code Is Not Null Then
904 IF l_debug_level > 0 THEN
905 oe_debug_pub.add('line will not be acknowledged.');
906 END IF;
907 l_line_tbl(I).changed_lines_pocao := 'N';
908 --EXIT;
909 End If;
910 END IF;
911 l_validation_org_id := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
912 IF l_debug_level > 0 THEN
913 oe_debug_pub.add( 'FIRST_ACK_CODE FROM PROCAPI = '||L_LINE_TBL ( I ) .FIRST_ACK_CODE ) ;
914 END IF;
915
916 BEGIN
917 -- Fix for the bug2722519
918 SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
919 INTO l_ship_to_addr_id, l_ship_to_location_code,l_ship_to_addr_code
920 FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
921 WHERE a.cust_acct_site_id = b.cust_acct_site_id
922 AND b.site_use_id = l_line_tbl(I).ship_to_org_id
923 AND b.site_use_code='SHIP_TO';
924 -- removed unnecessary validation of customer account, bug 3656640
925
926 IF l_debug_level > 0 THEN
927 oe_debug_pub.add( 'SHIP-LINE- TO ADDR ID = '||L_SHIP_TO_ADDR_ID ) ;
928 END IF;
929 EXCEPTION
930 WHEN OTHERS THEN
931 NULL;
932 IF l_debug_level > 0 THEN
933 oe_debug_pub.add( 'UNABLE TO DERIVE SHIP_TO ADDR FOR LINE' ) ;
934 END IF;
935 END;
936
937 get_address(
938 p_address_type_in => 'CUSTOMER',
939 p_org_id_in => l_validation_org_id,
940 p_address_id_in => l_ship_to_addr_id,
941 p_tp_location_code_in => NULL,
942 p_tp_translator_code_in => NULL,
943 l_addr1 => l_addr1,
944 l_addr2 => l_addr2,
945 l_addr3 => l_addr3,
946 l_addr4 => l_addr4,
947 l_addr_alt => l_addr_alt,
948 l_city => l_city,
949 l_county => l_county,
950 l_state => l_state,
951 l_zip => l_zip,
952 l_province => l_province,
953 l_country => l_country,
954 l_region1 => l_region1,
955 l_region2 => l_region2,
956 l_region3 => l_region3,
957 x_return_status => x_return_status);
958
959 l_line_val_tbl(I).ship_to_address1 := SUBSTR(l_addr1,0,240);
960 l_line_val_tbl(I).ship_to_address2 := SUBSTR(l_addr2,0,240);
961 l_line_val_tbl(I).ship_to_address3 := SUBSTR(l_addr3,0,240);
962 l_line_val_tbl(I).ship_to_address4 := SUBSTR(l_addr4,0,240);
963 l_line_val_tbl(I).ship_to_state := SUBSTR(l_state,0,60);
964 l_line_val_tbl(I).ship_to_city := SUBSTR(l_city,0,60);
965 l_line_val_tbl(I).ship_to_zip := SUBSTR(l_zip,0,60);
966 l_line_val_tbl(I).ship_to_country := SUBSTR(l_country,0,60);
967 l_line_val_tbl(I).ship_to_county := SUBSTR(l_county,0,60);
968 l_line_val_tbl(I).ship_to_province := SUBSTR(l_province,0,60);
969 l_line_val_tbl(I).ship_to_location := l_ship_to_addr_code;
970 l_line_tbl(I).ship_to_edi_location_code := l_ship_to_location_code;
971 /* Code for Blanket Sales Orders */
972 IF l_debug_level > 0 THEN
973 oe_debug_pub.add( 'Entering into Blanket related model code') ;
974 END IF;
975 IF l_line_tbl(I).item_type_code = 'INCLUDED' THEN
976 l_top_model_line_id := l_line_tbl(I).top_model_line_id;
977 OPEN GET_TOP_MODEL(l_top_model_line_id);
978 FETCH GET_TOP_MODEL
979 INTO
980 l_blanket_number,
981 l_blanket_line_number;
982 IF GET_TOP_MODEL%NOTFOUND THEN
983 IF l_debug_level > 0 THEN
984 oe_debug_pub.add( 'Blanket number and Blanket line number NOT FOUND for the cursor GET_TOP_MODEL' ) ;
985 END IF;
986 END IF;
987 CLOSE GET_TOP_MODEL;
988 l_line_tbl(I).blanket_number := l_blanket_number;
989 l_line_tbl(I).blanket_line_number := l_blanket_line_number;
990 END IF;
991
992 IF l_debug_level > 0 THEN
993 oe_debug_pub.add( 'Leaving Blanket related code for the Top Model Line Id: '||l_top_model_line_id) ;
994 oe_debug_pub.add( 'Leaving Blanket related code Blanket Number : '||l_blanket_number) ;
995 oe_debug_pub.add( 'Leaving Blanket related code Blanket Line Number : '||l_blanket_line_number) ;
996 END IF;
997
998 IF l_debug_level > 0 THEN
999 oe_debug_pub.add( 'SHIP TO EDI CODE = '||L_SHIP_TO_LOCATION_CODE ) ;
1000 END IF;
1001 IF l_debug_level > 0 THEN
1002 oe_debug_pub.add( 'ADDR1 = '||L_LINE_VAL_TBL ( I ) .SHIP_TO_ADDRESS1 ) ;
1003 END IF;
1004 IF l_debug_level > 0 THEN
1005 oe_debug_pub.add( 'SHIP TO CONTACT ID = '||L_LINE_TBL ( I ) .SHIP_TO_CONTACT_ID ) ;
1006 END IF;
1007 IF l_debug_level > 0 THEN
1008 oe_debug_pub.add( 'ACTUAL_SHIPMENT_DATE = '||L_LINE_TBL ( I ) .ACTUAL_SHIPMENT_DATE ) ;
1009 END IF;
1010 IF l_debug_level > 0 THEN
1011 oe_debug_pub.add( 'ACTUAL_ARRIVAL_DATE = '||L_LINE_TBL ( I ) .ACTUAL_ARRIVAL_DATE ) ;
1012 END IF;
1013 IF l_debug_level > 0 THEN
1014 oe_debug_pub.add( 'CUSTOMER_DOCK_CODE = '||L_LINE_TBL ( I ) .CUSTOMER_DOCK_CODE ) ;
1015 END IF;
1016 IF l_debug_level > 0 THEN
1017 oe_debug_pub.add( 'CUST_PRODUCTION_SEQ_NUM = '||L_LINE_TBL ( I ) .CUST_PRODUCTION_SEQ_NUM ) ;
1018 END IF;
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add( 'CUSTOMER_PRODUCTION_LINE = '||L_LINE_TBL ( I ) .CUSTOMER_PRODUCTION_LINE ) ;
1021 END IF;
1022 IF l_debug_level > 0 THEN
1023 oe_debug_pub.add( 'CUST_MODEL_SERIAL_NUMBER = '||L_LINE_TBL ( I ) .CUST_MODEL_SERIAL_NUMBER ) ;
1024 END IF;
1025 IF l_debug_level > 0 THEN
1026 oe_debug_pub.add( 'CUSTOMER_JOB = '||L_LINE_TBL ( I ) .CUSTOMER_JOB ) ;
1027 END IF;
1028 IF l_debug_level > 0 THEN
1029 oe_debug_pub.add( 'CUSTOMER_PAYMENT_TERM = '||L_LINE_VAL_TBL ( I ) .CUSTOMER_PAYMENT_TERM ) ;
1030 END IF;
1031
1032
1033 BEGIN
1034 SELECT a.person_last_name, a.person_first_name
1035 INTO l_line_val_tbl(I).ship_to_contact_last_name,
1036 l_line_val_tbl(I).ship_to_contact_first_name
1037 FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
1038 WHERE c.cust_account_role_id = l_header_rec.sold_to_contact_id
1039 AND c.party_id=b.party_id
1040 AND b.subject_id=a.party_id
1041 AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
1042 AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1043 AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1044 AND b.DIRECTIONAL_FLAG = 'F';
1045 IF l_debug_level > 0 THEN
1046 oe_debug_pub.add( 'SHIP TO CONTACT = '||L_LINE_VAL_TBL ( I ) .SHIP_TO_CONTACT_LAST_NAME ) ;
1047 END IF;
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 NULL;
1051 IF l_debug_level > 0 THEN
1052 oe_debug_pub.add( 'UNABLE TO GET FIRST/LAST NAME FOR LINE SHIP_TO' ) ;
1053 END IF;
1054 END;
1055
1056 BEGIN
1057 SELECT concatenated_segments
1058 INTO l_concatenated_segments
1059 FROM mtl_system_items_vl
1060 WHERE inventory_item_id = l_line_tbl(I).inventory_item_id
1061 AND organization_id = l_validation_org_id;
1062 l_line_val_tbl(I).inventory_item := l_concatenated_segments;
1063 IF l_debug_level > 0 THEN
1064 oe_debug_pub.add( 'ITEM ID = '||L_LINE_TBL ( I ) .INVENTORY_ITEM_ID ) ;
1065 END IF;
1066 IF l_debug_level > 0 THEN
1067 oe_debug_pub.add( 'ITEM NAME = '||L_LINE_VAL_TBL ( I ) .INVENTORY_ITEM ) ;
1068 END IF;
1069 IF l_debug_level > 0 THEN
1070 oe_debug_pub.add( 'INDEX IS = '||I ) ;
1071 END IF;
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 NULL;
1075 IF l_debug_level > 0 THEN
1076 oe_debug_pub.add( 'UNABLE TO GET ITEM NAME' ) ;
1077 END IF;
1078 END;
1079 /* 1944673 */
1080 l_line_val_tbl(i).line_type := OE_Id_To_Value.Line_Type
1081 ( p_line_type_id => l_line_tbl(i).line_type_id );
1082
1083 l_line_val_tbl(i).price_list := OE_Id_To_Value.price_list
1084 ( p_price_list_id => l_line_tbl(i).price_list_id );
1085
1086 l_line_val_tbl(i).salesrep := OE_Id_To_Value.salesrep
1087 ( p_salesrep_id => l_line_tbl(i).salesrep_id );
1088
1089 l_line_val_tbl(i).fob_point := OE_Id_To_Value.Fob_Point
1090 ( p_Fob_Point_code => l_line_tbl(i).fob_point_code );
1091
1092 l_line_val_tbl(i).freight_terms := OE_Id_To_Value.freight_terms
1093 ( p_freight_terms_code => l_line_tbl(i).freight_terms_code );
1094
1095 l_line_val_tbl(i).Agreement := OE_Id_To_Value.Agreement
1096 ( p_agreement_id => l_line_tbl(i).agreement_id );
1097
1098 l_line_val_tbl(i).payment_term := OE_Id_To_Value.payment_term
1099 ( p_payment_term_id => l_line_tbl(i).payment_term_id );
1100
1101 --sol_ord_er #16014165 start
1102 IF (l_line_tbl(i).service_subs_template_id IS NOT NULL AND
1103 l_line_tbl(i).service_subs_template_id <> FND_API.G_MISS_NUM)
1104 THEN
1105 l_line_val_tbl(i).service_subs_template := OE_ID_TO_VALUE.Subscription_Template(p_service_subs_template_id => l_line_tbl(i).service_subs_template_id);
1106 END IF;
1107
1108 IF (l_line_tbl(i).service_cov_template_id IS NOT NULL AND
1109 l_line_tbl(i).service_cov_template_id <> FND_API.G_MISS_NUM)
1110 THEN
1111 l_line_val_tbl(i).service_cov_template := OE_ID_TO_VALUE.Coverage_Template(p_service_cov_template_id => l_line_tbl(i).service_cov_template_id);
1112 END IF;
1113
1114 IF (l_line_tbl(i).service_bill_profile_id IS NOT NULL AND
1115 l_line_tbl(i).service_bill_profile_id <> FND_API.G_MISS_NUM)
1116 THEN
1117 l_line_val_tbl(i).service_bill_profile := OE_ID_TO_VALUE.Billing_Profile(p_service_bill_profile_id => l_line_tbl(i).service_bill_profile_id);
1118 END IF;
1119
1120 IF (l_line_tbl(i).service_bill_option_code IS NOT NULL AND
1121 l_line_tbl(i).service_bill_option_code <> FND_API.G_MISS_CHAR)
1122 THEN
1123 l_line_val_tbl(i).service_bill_option :=OE_ID_TO_VALUE.Billing_Option(p_service_bill_option_code => l_line_tbl(i).service_bill_option_code);
1124 END IF;
1125 --sol_ord_er #16014165 end
1126
1127 --Added for bug 4034441 start
1128 Oe_Id_To_Value.End_Customer( p_end_customer_id => l_line_tbl(i).end_customer_id
1129 , x_end_customer_name => l_line_val_tbl(i).end_customer_name
1130 , x_end_customer_number => l_line_val_tbl(i).end_customer_number
1131 );
1132 l_line_val_tbl(i).end_customer_contact := Oe_Id_To_Value.End_Customer_Contact(p_end_customer_contact_id => l_line_tbl(i).end_customer_contact_id);
1133
1134 OE_ID_TO_VALUE.End_Customer_Site_Use( p_end_customer_site_use_id => l_line_tbl(i).end_customer_site_use_id
1135 , x_end_customer_address1 => l_line_val_tbl(i).end_customer_site_address1
1136 , x_end_customer_address2 => l_line_val_tbl(i).end_customer_site_address2
1137 , x_end_customer_address3 => l_line_val_tbl(i).end_customer_site_address3
1138 , x_end_customer_address4 => l_line_val_tbl(i).end_customer_site_address4
1139 , x_end_customer_location => l_line_val_tbl(i).end_customer_site_location
1140 , x_end_customer_city => l_line_val_tbl(i).end_customer_site_city
1141 , x_end_customer_state => l_line_val_tbl(i).end_customer_site_state
1142 , x_end_customer_postal_code => l_line_val_tbl(i).end_customer_site_postal_code
1143 , x_end_customer_country => l_line_val_tbl(i).end_customer_site_country
1144 );
1145 --Added for bug 4034441 end
1146
1147
1148 /* -----------------------------------------------------------------
1149 Derive ship_from address for Lines Bug #2116166
1150 -----------------------------------------------------------------
1151 */
1152 BEGIN
1153 select hu.location_id,hl.ece_tp_location_code, hl.location_code
1154 into l_ship_from_addr_id, l_ship_from_location_code,l_ship_from_addr_code
1155 from hr_all_organization_units hu,
1156 hr_locations hl
1157 where hl.location_id = hu.location_id
1158 AND hu.organization_id = l_line_tbl(I).ship_from_org_id;
1159
1160 IF l_debug_level > 0 THEN
1161 oe_debug_pub.add( 'SHIP-FROM LINE ADDR ID = '||L_SHIP_FROM_ADDR_ID ) ;
1162 END IF;
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 NULL;
1166 IF l_debug_level > 0 THEN
1167 oe_debug_pub.add( 'UNABLE TO DERIVE SHIP_FROM ADDR FOR LINE' ) ;
1168 END IF;
1169 END;
1170
1171 get_address(
1172 p_address_type_in => 'HR_LOCATION',
1173 p_org_id_in => l_validation_org_id,
1174 p_address_id_in => l_ship_from_addr_id,
1175 p_tp_location_code_in => NULL,
1176 p_tp_translator_code_in => NULL,
1177 l_addr1 => l_addr1,
1178 l_addr2 => l_addr2,
1179 l_addr3 => l_addr3,
1180 l_addr4 => l_addr4,
1181 l_addr_alt => l_addr_alt,
1182 l_city => l_city,
1183 l_county => l_county,
1184 l_state => l_state,
1185 l_zip => l_zip,
1186 l_province => l_province,
1187 l_country => l_country,
1188 l_region1 => l_region1,
1189 l_region2 => l_region2,
1190 l_region3 => l_region3,
1191 x_return_status => x_return_status);
1192
1193 l_line_val_tbl(I).ship_from_address1 := SUBSTR(l_addr1,0,240);
1194 l_line_val_tbl(I).ship_from_address2 := SUBSTR(l_addr2,0,240);
1195 l_line_val_tbl(I).ship_from_address3 := SUBSTR(l_addr3,0,240);
1196 l_line_val_tbl(I).ship_from_address4 := SUBSTR(l_addr4,0,240);
1197 l_line_val_tbl(I).ship_from_region1 := SUBSTR(l_region1,0,240);
1198 l_line_val_tbl(I).ship_from_city := SUBSTR(l_city,0,60);
1199 l_line_val_tbl(I).ship_from_postal_code := SUBSTR(l_zip,0,60);
1200 l_line_val_tbl(I).ship_from_country := SUBSTR(l_country,0,60);
1201 l_line_val_tbl(I).ship_from_region2 := SUBSTR(l_region2,0,240);
1202 l_line_val_tbl(I).ship_from_region3 := SUBSTR(l_region3,0,240);
1203 l_line_val_tbl(I).ship_from_org := l_ship_from_addr_code;
1204 l_line_tbl(I).ship_from_edi_location_code := SUBSTR(l_ship_from_location_code,0,40);
1205 IF l_debug_level > 0 THEN
1206 oe_debug_pub.add( 'ADDR1 = '||L_LINE_VAL_TBL ( I ) .SHIP_FROM_ADDRESS1 ) ;
1207 END IF;
1208 IF l_debug_level > 0 THEN
1209 oe_debug_pub.add( 'INSIDE WHILE LOOP I = '||I ) ;
1210 END IF;
1211
1212 if (l_line_tbl(I).operation is NULL OR
1213 l_line_tbl(I).operation = FND_API.G_MISS_CHAR )
1214 THEN l_line_tbl(I).operation := NULL;
1215 end if;
1216 i:= l_line_tbl.next(i);
1217 END LOOP;
1218
1219 /* -----------------------------------------------------------------
1220 Derive ship_from address for Header Bug #2116166
1221 -----------------------------------------------------------------
1222 */
1223 BEGIN
1224 select hu.location_id,hl.ece_tp_location_code, hl.location_code
1225 into l_ship_from_addr_id, l_ship_from_location_code,l_ship_from_addr_code
1226 from hr_all_organization_units hu,
1227 hr_locations hl
1228 where hl.location_id = hu.location_id
1229 AND hu.organization_id = l_header_rec.ship_from_org_id;
1230
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'SHIP-FROM HEADER ADDR ID = '||L_SHIP_FROM_ADDR_ID ) ;
1233 END IF;
1234 IF l_debug_level > 0 THEN
1235 oe_debug_pub.add( 'SHIP-FROM HEADER SHIP_FROM ORG = '||L_HEADER_REC.SHIP_FROM_ORG_ID ) ;
1236 END IF;
1237 IF l_debug_level > 0 THEN
1238 oe_debug_pub.add( 'SHIP-FROM HEADER EDI LOC CODE = '||L_SHIP_FROM_LOCATION_CODE ) ;
1239 END IF;
1240 IF l_debug_level > 0 THEN
1241 oe_debug_pub.add( 'SHIP-FROM HEADER ADDR CODE = '||L_SHIP_FROM_ADDR_CODE ) ;
1242 END IF;
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 NULL;
1246 IF l_debug_level > 0 THEN
1247 oe_debug_pub.add( 'UNABLE TO DERIVE SHIP_FROM ADDR FOR HEADER' ) ;
1248 END IF;
1249 END;
1250
1251 get_address(
1252 p_address_type_in => 'HR_LOCATION',
1253 p_org_id_in => l_header_rec.ship_from_org_id,
1254 p_address_id_in => l_ship_from_addr_id,
1255 p_tp_location_code_in => NULL,
1256 p_tp_translator_code_in => NULL,
1257 l_addr1 => l_addr1,
1258 l_addr2 => l_addr2,
1259 l_addr3 => l_addr3,
1260 l_addr4 => l_addr4,
1261 l_addr_alt => l_addr_alt,
1262 l_city => l_city,
1263 l_county => l_county,
1264 l_state => l_state,
1265 l_zip => l_zip,
1266 l_province => l_province,
1267 l_country => l_country,
1268 l_region1 => l_region1,
1269 l_region2 => l_region2,
1270 l_region3 => l_region3,
1271 x_return_status => x_return_status);
1272
1273 l_header_val_rec.ship_from_address1 := SUBSTR(l_addr1,0,240);
1274 l_header_val_rec.ship_from_address2 := SUBSTR(l_addr2,0,240);
1275 l_header_val_rec.ship_from_address3 := SUBSTR(l_addr3,0,240);
1276 l_header_val_rec.ship_from_address4 := SUBSTR(l_addr4,0,240);
1277 l_header_val_rec.ship_from_region1 := SUBSTR(l_region1,0,240);
1278 l_header_val_rec.ship_from_city := SUBSTR(l_city,0,60);
1279 l_header_val_rec.ship_from_postal_code := SUBSTR(l_zip,0,60);
1280 l_header_val_rec.ship_from_country := SUBSTR(l_country,0,60);
1281 l_header_val_rec.ship_from_region2 := SUBSTR(l_region2,0,240);
1282 l_header_val_rec.ship_from_region3 := SUBSTR(l_region3,0,240);
1283 l_header_val_rec.ship_from_org := l_ship_from_addr_code;
1284 l_header_rec.ship_from_edi_location_code := l_ship_from_location_code;
1285 IF l_debug_level > 0 THEN
1286 oe_debug_pub.add( 'ADDR1 = '||L_HEADER_VAL_REC.SHIP_FROM_ADDRESS1 ) ;
1287 END IF;
1288
1289 /* -----------------------------------------------------------------
1290 -- IF the local variable is not set above meaning that data at line
1291 -- level has not changed for sending acknowledgments, check
1292 -- at header level also, else the following check is not required.
1293 -- -----------------------------------------------------------------
1294 */
1295 IF l_ack_req_flag = 'N' And
1296 l_header_rec_isnull = 'N' THEN
1297 IF NOT (OE_GLOBALS.Equal(l_header_rec.cust_po_number,
1298 l_old_header_rec.cust_po_number)
1299 AND OE_GLOBALS.Equal(l_header_rec.ship_to_org_id,
1300 l_old_header_rec.ship_to_org_id)
1301 AND OE_GLOBALS.Equal(l_header_rec.ordered_date,
1302 l_old_header_rec.ordered_date))
1303
1304 THEN
1305 l_ack_req_flag := 'H';
1306 END IF;
1307 END IF;
1308
1309
1310 /* ------------------------------------------------------
1311 Derive sold_to_location for header
1312 -----------------------------------------------------
1313 */
1314 BEGIN
1315 IF l_debug_level > 0 THEN
1316 oe_debug_pub.add( 'SOLD_TO_SITE_USE_ID: ' || l_header_rec.sold_to_site_use_id) ;
1317 END IF;
1318
1319 OE_ID_TO_VALUE.Customer_Location(p_sold_to_site_use_id => l_header_rec.sold_to_site_use_id,
1320 x_sold_to_location_address1 => l_header_val_rec.sold_to_location_address1,
1321 x_sold_to_location_address2 => l_header_val_rec.sold_to_location_address2,
1322 x_sold_to_location_address3 => l_header_val_rec.sold_to_location_address3,
1323 x_sold_to_location_address4 => l_header_val_rec.sold_to_location_address4,
1324 x_sold_to_location => l_header_val_rec.sold_to_location,
1325 x_sold_to_location_city => l_header_val_rec.sold_to_location_city,
1326 x_sold_to_location_state => l_header_val_rec.sold_to_location_state,
1327 x_sold_to_location_postal => l_header_val_rec.sold_to_location_postal,
1328 x_sold_to_location_country => l_header_val_rec.sold_to_location_country);
1329
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 NULL;
1333 IF l_debug_level > 0 THEN
1334 oe_debug_pub.add( 'UNABLE TO DERIVE SOLD_TO_LOCATION FOR HEADER' ) ;
1335 END IF;
1336 END;
1337
1338
1339 --Added for bug 4034441 start
1340 /* ------------------------------------------------------
1341 Derive end_customer for header
1342 -----------------------------------------------------
1343 */
1344
1345 BEGIN
1346 IF l_debug_level > 0 THEN
1347 oe_debug_pub.add( 'END CUSTOMER ID: ' || l_header_rec.end_customer_id) ;
1348 END IF;
1349
1350 OE_ID_TO_VALUE.End_Customer( p_end_customer_id => l_header_rec.end_customer_id
1351 , x_end_customer_name => l_header_val_rec.end_customer_name
1352 , x_end_customer_number => l_header_val_rec.end_customer_number
1353 );
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 NULL;
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add( 'UNABLE TO DERIVE END CUSTOMER FOR HEADER' ) ;
1360 END IF;
1361 END;
1362
1363
1364
1365 /* ------------------------------------------------------
1366 Derive end_customer contact for header
1367 -----------------------------------------------------
1368 */
1369
1370 BEGIN
1371 IF l_debug_level > 0 THEN
1372 oe_debug_pub.add( 'END CUSTOMER CONTACT ID: ' || l_header_rec.end_customer_contact_id) ;
1373 END IF;
1374
1375 l_header_val_rec.end_customer_contact := OE_ID_TO_VALUE.End_Customer_Contact( p_end_customer_contact_id => l_header_rec.end_customer_contact_id);
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 NULL;
1380 IF l_debug_level > 0 THEN
1381 oe_debug_pub.add( 'UNABLE TO DERIVE END CUSTOMER CONTACT FOR HEADER' ) ;
1382 END IF;
1383 END;
1384
1385
1386
1387 /* ------------------------------------------------------
1388 Derive end_customer location for header
1389 -----------------------------------------------------
1390 */
1391
1392 BEGIN
1393 IF l_debug_level > 0 THEN
1394 oe_debug_pub.add( 'END CUSTOMER SITE USE ID: ' || l_header_rec.end_customer_site_use_id) ;
1395 END IF;
1396
1397 OE_ID_TO_VALUE.End_Customer_Site_Use( p_end_customer_site_use_id => l_header_rec.end_customer_site_use_id
1398 , x_end_customer_address1 => l_header_val_rec.end_customer_site_address1
1399 , x_end_customer_address2 => l_header_val_rec.end_customer_site_address2
1400 , x_end_customer_address3 => l_header_val_rec.end_customer_site_address3
1401 , x_end_customer_address4 => l_header_val_rec.end_customer_site_address4
1402 , x_end_customer_location => l_header_val_rec.end_customer_site_location
1403 , x_end_customer_city => l_header_val_rec.end_customer_site_city
1404 , x_end_customer_state => l_header_val_rec.end_customer_site_state
1405 , x_end_customer_postal_code => l_header_val_rec.end_customer_site_postal_code
1406 , x_end_customer_country => l_header_val_rec.end_customer_site_country
1407 );
1408
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 NULL;
1412 IF l_debug_level > 0 THEN
1413 oe_debug_pub.add( 'UNABLE TO DERIVE END CUSTOMER LOCATION FOR HEADER' ) ;
1414 END IF;
1415 END;
1416 --Added for bug 4034441 end
1417
1418
1419
1420 /* ------------------------------------------------------
1421 Derive sold_to,ship_to and bill_to address for header
1422 -----------------------------------------------------
1423 */
1424
1425 BEGIN
1426 -- Fix for the bug 2722519
1427 SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
1428 INTO l_bill_to_addr_id, l_bill_to_location_code,l_bill_to_addr_code
1429 FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
1430 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1431 AND b.site_use_id = l_header_rec.invoice_to_org_id
1432 AND b.site_use_code='BILL_TO';
1433 -- removed unnecessary validation of customer account, bug 3656640
1434
1435 IF l_debug_level > 0 THEN
1436 oe_debug_pub.add( 'BILL TO ADDR ID = '||L_BILL_TO_ADDR_ID ) ;
1437 END IF;
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440 NULL;
1441 IF l_debug_level > 0 THEN
1442 oe_debug_pub.add( 'UNABLE TO DERIVE BILL_TO ADDR FOR HEAD' ) ;
1443 END IF;
1444 END;
1445
1446 BEGIN
1447 -- Fix for the bug 2722519
1448 SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
1449 INTO l_ship_to_addr_id, l_ship_to_location_code,l_ship_to_addr_code
1450 FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
1451 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1452 AND b.site_use_id = l_header_rec.ship_to_org_id
1453 AND b.site_use_code='SHIP_TO';
1454 -- removed unnecessary validation of customer account, bug 3656640
1455
1456 IF l_debug_level > 0 THEN
1457 oe_debug_pub.add( 'SHIP TO ADDR ID = '||L_SHIP_TO_ADDR_ID ) ;
1458 END IF;
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461 NULL;
1462 IF l_debug_level > 0 THEN
1463 oe_debug_pub.add( 'UNABLE TO DERIVE SHIP_TO ADDR FOR HEAD' ) ;
1464 END IF;
1465 END;
1466
1467 BEGIN
1468 SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
1469 INTO l_sold_to_addr_id, l_sold_to_location_code,l_sold_to_addr_code
1470 FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
1471 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1472 AND b.site_use_id = l_header_rec.sold_to_org_id
1473 AND b.site_use_code='SOLD_TO'
1474 AND a.cust_account_id = l_header_rec.sold_to_org_id;
1475 IF l_debug_level > 0 THEN
1476 oe_debug_pub.add( 'SOLD TO ADDR ID = '||L_SOLD_TO_ADDR_ID ) ;
1477 END IF;
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 NULL;
1482 IF l_debug_level > 0 THEN
1483 oe_debug_pub.add( 'UNABLE TO DERIVE SOLD_TO ADDR FOR HEAD' ) ;
1484 END IF;
1485 END;
1486
1487 get_address(
1488 p_address_type_in => 'CUSTOMER',
1489 p_org_id_in => l_validation_org_id,
1490 p_address_id_in => l_bill_to_addr_id,
1491 p_tp_location_code_in => NULL,
1492 p_tp_translator_code_in => NULL,
1493 l_addr1 => l_addr1,
1494 l_addr2 => l_addr2,
1495 l_addr3 => l_addr3,
1496 l_addr4 => l_addr4,
1497 l_addr_alt => l_addr_alt,
1498 l_city => l_city,
1499 l_county => l_county,
1500 l_state => l_state,
1501 l_zip => l_zip,
1502 l_province => l_province,
1503 l_country => l_country,
1504 l_region1 => l_region1,
1505 l_region2 => l_region2,
1506 l_region3 => l_region3,
1507 x_return_status => x_return_status);
1508 l_header_val_rec.invoice_to_address1 := SUBSTR(l_addr1,0,240);
1509 l_header_val_rec.invoice_to_address2 := SUBSTR(l_addr2,0,240);
1510 l_header_val_rec.invoice_to_address3 := SUBSTR(l_addr3,0,240);
1511 l_header_val_rec.invoice_to_address4 := SUBSTR(l_addr4,0,240);
1512 l_header_val_rec.invoice_to_state := SUBSTR(l_state,0,60);
1513 l_header_val_rec.invoice_to_city := SUBSTR(l_city,0,60);
1514 l_header_val_rec.invoice_to_zip := SUBSTR(l_zip,0,60);
1515 l_header_val_rec.invoice_to_country := SUBSTR(l_country,0,60);
1516 l_header_val_rec.invoice_to_county := SUBSTR(l_county,0,60);
1517 l_header_val_rec.invoice_to_province := SUBSTR(l_province,0,60);
1518 l_header_val_rec.invoice_to_location := l_bill_to_addr_code;
1519 l_header_val_rec.ship_to_location := l_ship_to_addr_code;
1520 l_header_val_rec.invoice_to_customer_number :=SUBSTR(l_sold_to_addr_code,0,30);
1521 l_header_rec.bill_to_edi_location_code := l_bill_to_location_code;
1522 l_header_rec.ship_to_edi_location_code := l_ship_to_location_code;
1523 l_header_rec.sold_to_edi_location_code := l_sold_to_location_code;
1524 IF l_debug_level > 0 THEN
1525 oe_debug_pub.add( 'SOLD TO LOCATION CODE = '||L_SOLD_TO_ADDR_CODE ) ;
1526 END IF;
1527 IF l_debug_level > 0 THEN
1528 oe_debug_pub.add( 'SHIP TO EDI CODE = '||L_SHIP_TO_LOCATION_CODE ) ;
1529 END IF;
1530 IF l_debug_level > 0 THEN
1531 oe_debug_pub.add( 'BILL TO EDI CODE = '||L_BILL_TO_LOCATION_CODE ) ;
1532 END IF;
1533 IF l_debug_level > 0 THEN
1534 oe_debug_pub.add( 'ADDR1 = '||L_HEADER_VAL_REC.INVOICE_TO_ADDRESS1 ) ;
1535 END IF;
1536
1537 get_address(
1538 p_address_type_in => 'CUSTOMER',
1539 p_org_id_in => l_validation_org_id,
1540 p_address_id_in => l_ship_to_addr_id,
1541 p_tp_location_code_in => NULL,
1542 p_tp_translator_code_in => NULL,
1543 l_addr1 => l_addr1,
1544 l_addr2 => l_addr2,
1545 l_addr3 => l_addr3,
1546 l_addr4 => l_addr4,
1547 l_addr_alt => l_addr_alt,
1548 l_city => l_city,
1549 l_county => l_county,
1550 l_state => l_state,
1551 l_zip => l_zip,
1552 l_province => l_province,
1553 l_country => l_country,
1554 l_region1 => l_region1,
1555 l_region2 => l_region2,
1556 l_region3 => l_region3,
1557 x_return_status => x_return_status);
1558
1559 l_header_val_rec.ship_to_address1 := SUBSTR(l_addr1,0,240);
1560 l_header_val_rec.ship_to_address2 := SUBSTR(l_addr2,0,240);
1561 l_header_val_rec.ship_to_address3 := SUBSTR(l_addr3,0,240);
1562 l_header_val_rec.ship_to_address4 := SUBSTR(l_addr4,0,240);
1563 l_header_val_rec.ship_to_state := SUBSTR(l_state,0,60);
1564 l_header_val_rec.ship_to_city := SUBSTR(l_city,0,60);
1565 l_header_val_rec.ship_to_zip := SUBSTR(l_zip,0,60);
1566 l_header_val_rec.ship_to_country := SUBSTR(l_country,0,60);
1567 l_header_val_rec.ship_to_county := SUBSTR(l_county,0,60);
1568 l_header_val_rec.ship_to_province := SUBSTR(l_province,0,240);
1569 IF l_debug_level > 0 THEN
1570 oe_debug_pub.add( 'ADDR1 = '||L_HEADER_VAL_REC.SHIP_TO_ADDRESS1 ) ;
1571 END IF;
1572
1573 get_address(
1574 p_address_type_in => 'CUSTOMER',
1575 p_org_id_in => l_validation_org_id,
1576 p_address_id_in => G_CURR_ADDRESS_ID,
1577 p_tp_location_code_in => NULL,
1578 p_tp_translator_code_in => NULL,
1579 l_addr1 => l_addr1,
1580 l_addr2 => l_addr2,
1581 l_addr3 => l_addr3,
1582 l_addr4 => l_addr4,
1583 l_addr_alt => l_addr_alt,
1584 l_city => l_city,
1585 l_county => l_county,
1586 l_state => l_state,
1587 l_zip => l_zip,
1588 l_province => l_province,
1589 l_country => l_country,
1590 l_region1 => l_region1,
1591 l_region2 => l_region2,
1592 l_region3 => l_region3,
1593 x_return_status => x_return_status);
1594
1595 l_header_val_rec.sold_to_address1 := SUBSTR(l_addr1,0,240);
1596 l_header_val_rec.sold_to_address2 := SUBSTR(l_addr2,0,240);
1597 l_header_val_rec.sold_to_address3 := SUBSTR(l_addr3,0,240);
1598 l_header_val_rec.sold_to_address4 := SUBSTR(l_addr4,0,240);
1599 l_header_val_rec.sold_to_state := SUBSTR(l_state,0,60);
1600 l_header_val_rec.sold_to_city := SUBSTR(l_city,0,60);
1601 l_header_val_rec.sold_to_zip := SUBSTR(l_zip,0,60);
1602 l_header_val_rec.sold_to_country := SUBSTR(l_country,0,60);
1603 l_header_val_rec.sold_to_county := SUBSTR(l_county,0,60);
1604 l_header_val_rec.sold_to_province := SUBSTR(l_province,0,240);
1605 IF l_debug_level > 0 THEN
1606 oe_debug_pub.add( 'ADDR1 = '||L_HEADER_VAL_REC.SOLD_TO_ADDRESS1 ) ;
1607 END IF;
1608 IF l_debug_level > 0 THEN
1609 oe_debug_pub.add( 'INV CONTACT ID = '||L_HEADER_REC.INVOICE_TO_CONTACT_ID ) ;
1610 END IF;
1611 IF l_debug_level > 0 THEN
1612 oe_debug_pub.add( 'SHIP TO CONTACT ID= '||L_HEADER_REC.SHIP_TO_CONTACT_ID ) ;
1613 END IF;
1614 IF l_debug_level > 0 THEN
1615 oe_debug_pub.add( 'SOLD TO CONTACT ID = '||L_HEADER_REC.SOLD_TO_CONTACT_ID ) ;
1616 END IF;
1617
1618 BEGIN
1619 SELECT a.person_last_name, a.person_first_name
1620 INTO l_header_val_rec.sold_to_contact_last_name, l_header_val_rec.sold_to_contact_first_name
1621 FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
1622 WHERE c.cust_account_role_id = l_header_rec.sold_to_contact_id
1623 AND c.party_id=b.party_id
1624 AND b.subject_id=a.party_id
1625 AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
1626 AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1627 AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1628 AND b.DIRECTIONAL_FLAG = 'F';
1629 IF l_debug_level > 0 THEN
1630 oe_debug_pub.add( 'SOLD TO CONTACT = '||L_HEADER_VAL_REC.SOLD_TO_CONTACT_LAST_NAME ) ;
1631 END IF;
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 NULL;
1635 IF l_debug_level > 0 THEN
1636 oe_debug_pub.add( 'UNABLE TO DERIVE SOLD_TO_CONTACT LAST NAME FOR HEAD' ) ;
1637 END IF;
1638 END;
1639
1640 BEGIN
1641
1642 SELECT a.person_last_name, a.person_first_name
1643 INTO l_header_val_rec.ship_to_contact_last_name, l_header_val_rec.ship_to_contact_first_name
1644 FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
1645 WHERE c.cust_account_role_id = l_header_rec.ship_to_contact_id
1646 AND c.party_id=b.party_id
1647 AND b.subject_id=a.party_id
1648 AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
1649 AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1650 AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1651 AND b.DIRECTIONAL_FLAG = 'F';
1652 IF l_debug_level > 0 THEN
1653 oe_debug_pub.add( 'SHIP TO CONTACT = '||L_HEADER_VAL_REC.SHIP_TO_CONTACT_LAST_NAME ) ;
1654 END IF;
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 NULL;
1658 IF l_debug_level > 0 THEN
1659 oe_debug_pub.add( 'UNABLE TO DERIVE SHIP_TO_CONTACT LAST NAME FOR HEAD' ) ;
1660 END IF;
1661 END;
1662 l_header_val_rec.order_type := OE_Id_To_Value.Order_Type
1663 ( p_order_type_id => l_header_rec.order_type_id );
1664
1665 IF l_debug_level > 0 THEN
1666 oe_debug_pub.add( 'L_HEADER_REC: '||L_HEADER_VAL_REC.ORDER_TYPE ) ;
1667 END IF;
1668 l_header_val_rec.payment_term := OE_Id_To_Value.Payment_Term
1669 ( p_payment_term_id => l_header_rec.payment_term_id );
1670 IF l_debug_level > 0 THEN
1671 oe_debug_pub.add( 'L_HEADER_REC: PAYMENT TERM :'||L_HEADER_VAL_REC.PAYMENT_TERM ) ;
1672 END IF;
1673
1674 l_header_val_rec.price_list := OE_Id_To_Value.Price_List
1675 ( p_price_list_id => l_header_rec.price_list_id );
1676 IF l_debug_level > 0 THEN
1677 oe_debug_pub.add( 'L_HEADER_REC: PRICE LIST '||L_HEADER_VAL_REC.PRICE_LIST ) ;
1678 END IF;
1679 IF l_debug_level > 0 THEN
1680 oe_debug_pub.add( 'L_HEADER_REC: ORDER_CATEGORY_CODE '||L_HEADER_REC.ORDER_CATEGORY_CODE ) ;
1681 END IF;
1682
1683 l_header_val_rec.salesrep := OE_Id_To_Value.salesrep
1684 ( p_salesrep_id => l_header_rec.salesrep_id
1685 );
1686
1687 l_header_val_rec.fob_point := OE_Id_To_Value.fob_point
1688 ( p_fob_point_code => l_header_rec.fob_point_code
1689 );
1690
1691 l_header_val_rec.freight_terms := OE_Id_To_Value.freight_terms
1692 ( p_freight_terms_code => l_header_rec.freight_terms_code
1693 );
1694
1695 l_header_val_rec.agreement := OE_Id_To_Value.agreement
1696 ( p_agreement_id => l_header_rec.agreement_id
1697 );
1698
1699 l_header_val_rec.conversion_type := OE_Id_To_Value.Conversion_Type
1700 ( p_conversion_type_code => l_header_rec.conversion_type_code
1701 );
1702
1703 l_header_val_rec.tax_exempt_reason := OE_Id_To_Value.Tax_Exempt_Reason
1704 ( p_tax_exempt_reason_code => l_header_rec.tax_exempt_reason_code
1705 );
1706
1707 l_header_val_rec.tax_point := OE_Id_To_Value.Tax_Point
1708 ( p_tax_point_code => l_header_rec.tax_point_code
1709 );
1710
1711 l_header_val_rec.invoicing_rule := OE_Id_To_Value.Invoicing_Rule
1712 ( p_invoicing_rule_id => l_header_rec.invoicing_rule_id
1713 );
1714
1715 OE_Id_To_Value.Sold_To_Org
1716 ( p_sold_to_org_id => l_header_rec.sold_to_org_id
1717 , x_org => l_header_val_rec.sold_to_org
1718 , x_customer_number => l_header_val_rec.customer_number
1719 );
1720
1721 Oe_Id_To_Value.Deliver_To_Org -- For bug 2701018
1722 ( p_deliver_to_org_id => l_header_rec.deliver_to_org_id
1723 , x_deliver_to_address1 => l_header_val_rec.deliver_to_address1
1724 , x_deliver_to_address2 => l_header_val_rec.deliver_to_address2
1725 , x_deliver_to_address3 => l_header_val_rec.deliver_to_address3
1726 , x_deliver_to_address4 => l_header_val_rec.deliver_to_address4
1727 , x_deliver_to_location => l_header_val_rec.deliver_to_location
1728 , x_deliver_to_org => l_header_val_rec.deliver_to_org
1729 , x_deliver_to_city => l_header_val_rec.deliver_to_city
1730 , x_deliver_to_state => l_header_val_rec.deliver_to_state
1731 , x_deliver_to_postal_code => l_header_val_rec.deliver_to_zip
1732 , x_deliver_to_country => l_header_val_rec.deliver_to_country
1733 );
1734 l_header_val_rec.deliver_to_customer_number := NULL; --For bug 2701018
1735
1736 l_header_val_rec.sold_to_contact := OE_Id_To_Value.Sold_To_Contact
1737 ( p_sold_to_contact_id => l_header_rec.sold_to_contact_id
1738 );
1739
1740 l_header_val_rec.ship_to_contact := OE_Id_To_Value.Ship_To_Contact
1741 ( p_ship_to_contact_id => l_header_rec.ship_to_contact_id
1742 );
1743
1744 if (l_header_val_rec.customer_payment_term is NULL OR
1745 l_header_val_rec.customer_payment_term = FND_API.G_MISS_CHAR )
1746 THEN l_header_val_rec.customer_payment_term := NULL;
1747 end if;
1748
1749 l_header_val_rec.ship_to_org :=l_header_val_rec.ship_to_location;
1750 l_header_val_rec.invoice_to_org :=l_header_val_rec.invoice_to_location;
1751
1752 -- for bug 3656640
1753 OE_ID_TO_VALUE.Ship_To_Customer_Name(p_ship_to_org_id => l_header_rec.ship_to_org_id,
1754 x_ship_to_customer_name => l_header_val_rec.ship_to_customer_name);
1755
1756 -- for bug 4489065
1757 OE_ID_TO_VALUE.Invoice_To_Customer_Name(p_invoice_to_org_id => l_header_rec.invoice_to_org_id,
1758 x_invoice_to_customer_name => l_header_val_rec.invoice_to_customer_name);
1759
1760
1761 IF l_debug_level > 0 THEN
1762 oe_debug_pub.add( 'L_HEADER_REC: SALES REP '||L_HEADER_VAL_REC.SALESREP ) ;
1763 END IF;
1764
1765 BEGIN
1766 SELECT a.person_last_name, a.person_first_name
1767 INTO l_header_val_rec.invoice_to_contact_last_name, l_header_val_rec.invoice_to_contact_first_name
1768 FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
1769 WHERE c.cust_account_role_id = l_header_rec.invoice_to_contact_id
1770 AND c.party_id=b.party_id
1771 AND b.subject_id=a.party_id
1772 AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
1773 AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1774 AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1775 AND b.DIRECTIONAL_FLAG = 'F';
1776 IF l_debug_level > 0 THEN
1777 oe_debug_pub.add( 'INVOICE TO CONTACT = '||L_HEADER_VAL_REC.INVOICE_TO_CONTACT_LAST_NAME ) ;
1778 END IF;
1779
1780
1781 IF l_debug_level > 0 THEN
1782 oe_debug_pub.add( 'L_HEADER_REC: PAYMENT TERM :'||L_HEADER_VAL_REC.PAYMENT_TERM ) ;
1783 END IF;
1784
1785
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788 NULL;
1789 IF l_debug_level > 0 THEN
1790 oe_debug_pub.add( 'UNABLE TO DERIVE LAST/FIRST NAME FOR HEADER' ) ;
1791 END IF;
1792 END;
1793
1794 /* -------------------------------------------------------------
1795 Since now all the required information is there to create the
1796 acknowledgment records, start inserting the records based on
1797 the l_ack_req_flag value.
1798 -------------------------------------------------------------
1799 */
1800 IF l_debug_level > 0 THEN
1801 oe_debug_pub.add( 'L_ACK_REQ_FLAG = '||L_ACK_REQ_FLAG , 3 ) ;
1802 END IF;
1803
1804 IF l_ack_req_flag IN ('B', 'H')
1805 OR l_header_rec.first_ack_code is null THEN
1806 IF l_debug_level > 0 THEN
1807 oe_debug_pub.add( 'BEFORE INSERTING HEADER ACKNOWLEDGMENT RECORD' , 3 ) ;
1808 END IF;
1809 IF l_debug_level > 0 THEN
1810 oe_debug_pub.add( 'EDI LOCATION CODE'||L_HEADER_REC.SHIP_FROM_EDI_LOCATION_CODE ) ;
1811 END IF;
1812
1813 l_rejected_lines := 'Y';
1814 /* Bug 2416561 : Calling the Convert_Miss_To_Null for the id records */
1815 OE_Header_Util.Convert_Miss_To_Null(p_x_header_rec => l_header_rec);
1816
1817 OE_Header_Ack_Util.Insert_Row
1818 ( p_header_rec => l_header_rec
1819 , p_header_val_rec => l_header_val_rec
1820 , p_old_header_rec => l_old_header_rec
1821 , p_old_header_val_rec => l_old_header_val_rec
1822 , p_reject_order => p_reject_order
1823 , x_return_status => l_return_status
1824 );
1825
1826 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1829 RAISE FND_API.G_EXC_ERROR;
1830 END IF;
1831 END IF; -- End of IF l_ack_req_flag IN ('B', 'H')
1832
1833
1834 -- Insert Line Information
1835
1836 -- Check if line acknowledgment need to be sent
1837 IF l_debug_level > 0 THEN
1838 oe_debug_pub.add( 'CHECK IF LINE ACKNOWLEDGMENT NEED TO BE SENT' ) ;
1839 END IF;
1840 IF l_debug_level > 0 THEN
1841 oe_debug_pub.add( 'L_ACK_REQ_FLAG'|| L_ACK_REQ_FLAG ) ;
1842 END IF;
1843 IF l_debug_level > 0 THEN
1844 oe_debug_pub.add( 'L_HEADER_REC.FIRST_ACK_CODE :'||L_HEADER_REC.FIRST_ACK_CODE ) ;
1845 END IF;
1846 IF l_ack_req_flag = 'B'
1847 OR l_header_rec.first_ack_code is null THEN
1848 IF l_debug_level > 0 THEN
1849 oe_debug_pub.add( 'BEFORE INSERTING LINE ACKNOWLEDGMENT RECORD' , 3 ) ;
1850 END IF;
1851 OE_Line_Ack_Util.Insert_Row
1852 (p_line_tbl => l_line_tbl
1853 , p_line_val_tbl => l_line_val_tbl
1854 , p_old_line_tbl => l_old_line_tbl
1855 , p_old_line_val_tbl => l_old_line_val_tbl
1856 , p_reject_order => p_reject_order
1857 , x_return_status => l_return_status
1858 );
1859
1860 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1862 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1863 RAISE FND_API.G_EXC_ERROR;
1864 END IF;
1865
1866
1867 -- Insert Lots Information
1868
1869 IF l_debug_level > 0 THEN
1870 oe_debug_pub.add( 'BEFORE INSERTING LINE LOTSERIAL ACKNOWLEDGMENT RECORD' , 3 ) ;
1871 END IF;
1872
1873 OE_Lots_Ack_Util.Insert_Row
1874 ( p_lot_serial_tbl => l_lot_serial_tbl
1875 , p_old_lot_serial_tbl => l_old_lot_serial_tbl
1876 , p_lot_serial_val_tbl => l_lot_serial_val_tbl
1877 , p_old_lot_serial_val_tbl => l_old_lot_serial_val_tbl
1878 , p_line_tbl => l_line_tbl
1879 , p_old_line_tbl => l_old_line_tbl
1880 , p_reject_order => p_reject_order
1881 , x_return_status => l_return_status
1882 );
1883
1884 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1886 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1887 RAISE FND_API.G_EXC_ERROR;
1888 END IF;
1889
1890
1891 -- Call Get_Reject_Line to get rejected lines and Lotserials
1892 -- if l_rejected_line = 'Y'
1893
1894 IF l_debug_level > 0 THEN
1895 oe_debug_pub.add('before getting rejected lines' , 3 ) ;
1896 oe_debug_pub.add('Order_source_id ' || l_header_rec.order_source_id);
1897 oe_debug_pub.add('Request_id ' || l_header_rec.request_id);
1898 oe_debug_pub.add('Orig_Sys_Doc '|| l_header_rec.orig_sys_document_ref);
1899 oe_debug_pub.add('Change_Seq ' || l_header_rec.change_sequence);
1900 END IF;
1901
1902 OE_Rejected_Lines_Ack.Get_Rejected_Lines
1903 ( p_request_id => l_header_rec.request_id
1904 , p_order_source_id => l_header_rec.order_source_id
1905 , p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref
1906 , p_change_sequence => l_header_rec.change_sequence
1907 , x_rejected_line_tbl => l_reject_line_tbl
1908 , x_rejected_line_val_tbl => l_reject_line_val_tbl
1909 , x_rejected_lot_serial_tbl => l_reject_lot_serial_tbl
1910 , x_return_status => l_return_status
1911 , p_header_id => l_header_rec.header_id
1912 , p_sold_to_org => l_header_val_rec.sold_to_org
1913 , p_sold_to_org_id => l_header_rec.sold_to_org_id
1914 );
1915
1916 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1918 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1919 RAISE FND_API.G_EXC_ERROR;
1920 END IF;
1921
1922
1923 -- Insert rejected lines and lotserials only if got any rejected records
1924
1925 IF l_reject_line_tbl.COUNT > 0 THEN
1926 IF l_debug_level > 0 THEN
1927 oe_debug_pub.add( 'BEFORE INSERTING REJECTED LINES' , 3 ) ;
1928 END IF;
1929
1930 OE_Line_Ack_Util.Insert_Row
1931 (p_line_tbl => l_reject_line_tbl
1932 ,p_old_line_tbl => l_reject_line_tbl
1933 ,p_line_val_tbl => l_reject_line_val_tbl
1934 ,p_old_line_val_tbl => l_reject_line_val_tbl
1935 ,p_buyer_seller_flag => 'B'
1936 ,p_reject_order => l_create_rejects
1937 ,x_return_status => l_return_status
1938 );
1939
1940 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1942 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1943 RAISE FND_API.G_EXC_ERROR;
1944 END IF;
1945
1946 IF l_reject_lot_serial_tbl.COUNT > 0 THEN
1947 IF l_debug_level > 0 THEN
1948 oe_debug_pub.add( 'BEFORE INSERTING REJECTED LINE LOTSERIALS' , 3 ) ;
1949 END IF;
1950
1951 OE_Lots_Ack_Util.Insert_Row
1952 (p_lot_serial_tbl => l_reject_lot_serial_tbl
1953 ,p_lot_serial_val_tbl => l_reject_lot_serial_val_tbl
1954 ,p_old_lot_serial_tbl => l_reject_lot_serial_tbl
1955 ,p_old_lot_serial_val_tbl => l_reject_lot_serial_val_tbl
1956 ,p_line_tbl => l_reject_line_tbl
1957 ,p_old_line_tbl => l_reject_line_tbl
1958 ,p_reject_order => l_create_rejects
1959 ,x_return_status => l_return_status
1960 );
1961
1962 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1964 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1965 RAISE FND_API.G_EXC_ERROR;
1966 END IF;
1967 END IF; -- IF l_reject_lot_serial_tbl.COUNT > 0
1968 END IF; -- IF l_reject_line_tbl.COUNT > 0
1969 END IF; -- IF l_ack_req_flag = 'B'
1970
1971 -- Set return status
1972
1973 x_return_status := FND_API.G_RET_STS_SUCCESS;
1974
1975 IF l_debug_level > 0 THEN -- debug level changed to 0.5 for bug 13435459
1976 oe_debug_pub.add( 'EXITING OE_ACKNOWLEDGMENT_PVT.PROCESS_ACKNOWLEDGMENT', 0.5);
1977 END IF;
1978
1979 EXCEPTION
1980
1981 WHEN FND_API.G_EXC_ERROR THEN
1982 IF l_debug_level > 0 THEN
1983 oe_debug_pub.add( 'ENCOUNTERED ERROR EXCEPTION' , 2 ) ;
1984 END IF;
1985 x_return_status := FND_API.G_RET_STS_ERROR;
1986
1987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1988 IF l_debug_level > 0 THEN
1989 oe_debug_pub.add( 'ENCOUNTERED UNEXPECTED ERROR EXCEPTION'||SQLERRM , 1 ) ;
1990 END IF;
1991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1992
1993 WHEN OTHERS THEN
1994 IF l_debug_level > 0 THEN
1995 oe_debug_pub.add( 'ENCOUNTERED OTHERS ERROR EXCEPTION IN OE_ACKNOWLEDGMENT_PVT.PROCESS_ACKNOWLEDGMENT: '||SQLERRM , 1 ) ;
1996 END IF;
1997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1998
1999 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2000 THEN
2001 OE_MSG_PUB.Add_Exc_Msg
2002 (G_PKG_NAME, 'OE_Acknowledgment_Pvt.Process_Acknowledgment');
2003 END IF;
2004
2005
2006 END Process_Acknowledgment;
2007
2008
2009 Procedure Process_Acknowledgment
2010 (p_header_rec In OE_Order_Pub.Header_Rec_Type,
2011 p_line_tbl In OE_Order_Pub.Line_Tbl_Type,
2012 p_old_header_rec In OE_Order_Pub.Header_Rec_Type,
2013 p_old_line_tbl In OE_Order_Pub.Line_Tbl_Type,
2014 x_return_status Out NOCOPY /* file.sql.39 change */ VARCHAR2
2015 )
2016 Is
2017
2018 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2019 l_status Varchar2(1);
2020 l_industry Varchar2(1);
2021 l_o_schema Varchar2(30);
2022 l_booked_flag Varchar2(1);
2023 l_line_index Pls_Integer;
2024 l_header_rec OE_Order_Pub.Header_Rec_Type := p_header_rec;
2025 l_line_tbl OE_Order_Pub.Line_Tbl_Type := p_line_tbl;
2026 l_address_id Number;
2027 l_site_use_id Number;
2028 l_tp_ret Boolean;
2029 l_tp_ret_status Varchar2(200);
2030 l_msg_count Number;
2031 l_msg_data Varchar2(200);
2032 l_ack_req_flag Varchar2(1) := 'N';
2033 i Pls_Integer;
2034 j Pls_Integer;
2035 l_line_rec OE_Order_Pub.Line_Rec_Type ;
2036 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type := p_old_line_tbl;
2037 l_old_header_rec OE_Order_Pub.Header_Rec_Type := p_old_header_rec;
2038 l_return_status Varchar2(1);
2039 l_rejected_lines Varchar2(1);
2040
2041 l_reject_line_tbl OE_Order_Pub.Line_Tbl_Type;
2042 l_reject_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type;
2043 l_reject_Lot_Serial_tbl OE_Order_Pub.Lot_Serial_Tbl_Type;
2044 l_reject_Lot_Serial_val_tbl OE_Order_Pub.Lot_Serial_Val_Tbl_Type;
2045
2046 l_xml_message_id Number;
2047 l_ack_type Varchar2(30);
2048 l_sold_to_org Varchar2(360);
2049 l_customer_number Varchar2(30);
2050 l_raise_event Varchar2(1);
2051 l_message_text Varchar2(500);
2052 l_header_rec_isnull varchar2(1) := 'N';
2053
2054 -- bug 3439319
2055 l_sales_order_id Number;
2056 Begin
2057
2058 If l_debug_level > 0 Then
2059 Oe_Debug_Pub.Add('Entering New Process Acknowledgment');
2060 End If;
2061
2062 -- Check if EC is installed
2063 If Oe_Globals.G_EC_INSTALLED Is Null Then
2064 Oe_Globals.G_EC_INSTALLED := Oe_Globals.Check_Product_Installed(175);
2065 End If;
2066 If Oe_Globals.G_EC_INSTALLED <> 'Y' Then
2067 x_return_status := FND_API.G_RET_STS_SUCCESS;
2068 If l_debug_level > 0 Then
2069 oe_debug_pub.add('EC not installed');
2070 End If;
2071 -- Raise event to log message
2072 Return;
2073 End If;
2074
2075 If (l_header_rec.header_id <> FND_API.G_MISS_NUM And
2076 nvl(l_header_rec.header_id,0) <> 0) Then
2077 Begin
2078 Select booked_flag,xml_message_id
2079 Into l_booked_flag,l_xml_message_id
2080 From oe_order_headers
2081 Where header_id = l_header_rec.header_id;
2082 If l_booked_flag = 'Y' Then
2083 l_header_rec.booked_flag := l_booked_flag;
2084 End If;
2085 If l_header_rec.order_source_id = 0 And
2086 l_xml_message_id Is Null Then
2087 Select Oe_Xml_Message_Seq_S.nextval
2088 Into l_xml_message_id
2089 From dual;
2090 l_header_rec.xml_message_id := l_xml_message_id;
2091 End IF;
2092 Exception
2093 When Others Then
2094 x_return_status := FND_API.G_RET_STS_SUCCESS;
2095 If l_debug_level > 0 Then
2096 oe_debug_pub.add('Exception in getting booked flag');
2097 End If;
2098 Return;
2099 End;
2100 Elsif l_line_tbl.count > 0 And
2101 (l_header_rec.header_id = FND_API.G_MISS_NUM OR
2102 nvl(l_header_rec.header_id,0) = 0) AND
2103 (l_line_tbl(l_line_tbl.first).header_id <> FND_API.G_MISS_NUM AND
2104 nvl(l_line_tbl(l_line_tbl.first).header_id,0) <> 0) Then
2105 Begin
2106 l_line_index := l_line_tbl.First;
2107 -- start bug 4048709, if the global picture header record is not present
2108 -- that means that there were no header level changes
2109 -- but we still need the header record to perform tp check + send ack,
2110 -- so get it from the cache if possible
2111 IF l_debug_level > 0 THEN
2112 oe_debug_pub.add( 'Header Id in cache is : '||oe_order_cache.g_header_rec.header_id);
2113 END IF;
2114 IF OE_ORDER_CACHE.g_header_rec.header_id <> FND_API.G_MISS_NUM AND
2115 nvl(OE_ORDER_CACHE.g_header_rec.header_id,0) = l_line_tbl(l_line_index).header_id THEN
2116 l_header_rec := OE_ORDER_CACHE.g_header_rec;
2117 IF l_debug_level > 0 THEN
2118 oe_debug_pub.add('Assigned header record from cache with booked flag '|| l_header_rec.booked_flag);
2119 END IF;
2120 ELSE
2121 -- end bug 4048709
2122 OE_Header_Util.Query_Row
2123 (p_header_id => l_line_tbl(l_line_index).header_id,
2124 x_header_rec => l_header_rec
2125 );
2126 END IF;
2127 l_header_rec_isnull := 'Y';
2128 Exception
2129 When Others Then
2130 x_return_status := FND_API.G_RET_STS_SUCCESS;
2131 If l_debug_level > 0 Then
2132 oe_debug_pub.add('Exception in l_line_tbl.count > 0');
2133 End If;
2134 Return;
2135 End;
2136 Else
2137 -- Nothing to Acknowledge
2138 x_return_status := FND_API.G_RET_STS_SUCCESS;
2139 If l_debug_level > 0 Then
2140 oe_debug_pub.add('Exception else, Nothing to Acknowledge');
2141 End If;
2142 Return;
2143 End If;
2144
2145 If G_CURR_SOLD_TO_ORG_ID = l_header_rec.sold_to_org_id and
2146 (G_TP_RET = FALSE Or G_PRIMARY_SETUP = FALSE) Then
2147 x_return_status := FND_API.G_RET_STS_SUCCESS;
2148 If l_debug_level > 0 Then
2149 oe_debug_pub.add('Transaction Not Enabled for Trading Partner');
2150 End If;
2151 -- Raise event to log message
2152 /*
2153 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_SETUP_ERR');
2154 fnd_message.set_token ('TRANSACTION', l_header_rec.xml_transaction_type_code);
2155 l_message_text := fnd_message.get;
2156 OE_Acknowledgment_Pub.Raise_Event_XMLInt (
2157 p_order_source_id => l_header_rec.order_source_id,
2158 p_partner_document_num => l_header_rec.orig_sys_document_ref,
2159 p_sold_to_org_id => l_header_rec.sold_to_org_id,
2160 p_order_type_id => l_header_rec.order_type_id,
2161 p_transaction_type => 'ONT',
2162 p_transaction_subtype => l_header_rec.xml_transaction_type_code,
2163 p_itemtype => null,
2164 p_itemkey => null,
2165 p_xmlg_document_id => l_xml_message_id,
2166 p_message_text => l_message_text,
2167 p_document_num => l_header_rec.order_number,
2168 p_change_sequence => l_header_rec.change_sequence,
2169 p_org_id => l_header_rec.org_id,
2170 p_doc_status => 'ERROR',
2171 p_header_id => l_header_rec.header_id,
2172 x_return_status => l_return_status);
2173 */
2174 Return;
2175 End If;
2176
2177 If G_CURR_SOLD_TO_ORG_ID = l_header_rec.sold_to_org_id And
2178 G_CURR_ADDRESS_ID Is Not Null And
2179 G_TP_RET = TRUE Then
2180 goto create_ack;
2181 End If;
2182
2183 l_tp_ret := FALSE;
2184
2185 Begin
2186 Oe_Debug_Pub.Add('before select of sold to org id');
2187 Select /*MOAC_SQL_CHANGES*/ b.site_use_id, a.cust_acct_site_id
2188 Into l_site_use_id, l_address_id
2189 From hz_cust_site_uses b, hz_cust_acct_sites_all a
2190 Where a.cust_acct_site_id = b.cust_acct_site_id
2191 And a.cust_account_id = l_header_rec.sold_to_org_id
2192 And b.site_use_code = 'SOLD_TO'
2193 And b.primary_flag = 'Y'
2194 And b.status = 'A'
2195 And a.org_id=b.org_id
2196 And a.status = 'A';
2197
2198 G_CURR_SOLD_TO_ORG_ID := l_header_rec.sold_to_org_id;
2199 G_CURR_ADDRESS_ID := l_address_id;
2200 G_PRIMARY_SETUP := TRUE;
2201 Exception
2202 When NO_DATA_FOUND Then
2203 x_return_status := FND_API.G_RET_STS_SUCCESS;
2204 G_CURR_SOLD_TO_ORG_ID := l_header_rec.sold_to_org_id;
2205 G_CURR_ADDRESS_ID := NULL;
2206 G_PRIMARY_SETUP := FALSE;
2207
2208 If l_debug_level > 0 Then
2209 oe_debug_pub.add('No Primary Sold To set');
2210 End If;
2211 -- Raise event to log message
2212 /*
2213 fnd_message.set_name('ONT', 'OE_OI_TP_NOT_FOUND');
2214 fnd_message.set_token ('CUST_ID', l_header_rec.sold_to_org_id);
2215 l_message_text := fnd_message.get;
2216 OE_Acknowledgment_Pub.Raise_Event_XMLInt (
2217 p_order_source_id => l_header_rec.order_source_id,
2218 p_partner_document_num => l_header_rec.orig_sys_document_ref,
2219 p_sold_to_org_id => l_header_rec.sold_to_org_id,
2220 p_order_type_id => l_header_rec.order_type_id,
2221 p_transaction_type => 'ONT',
2222 p_transaction_subtype => l_header_rec.xml_transaction_type_code,
2223 p_itemtype => null,
2224 p_itemkey => null,
2225 p_xmlg_document_id => l_xml_message_id,
2226 p_message_text => l_message_text,
2227 p_document_num => l_header_rec.order_number,
2228 p_change_sequence => l_header_rec.change_sequence,
2229 p_org_id => l_header_rec.org_id,
2230 p_doc_status => 'ERROR',
2231 p_header_id => l_header_rec.header_id,
2232 x_return_status => l_return_status);
2233 */
2234 Return;
2235
2236 When Others Then
2237 x_return_status := FND_API.G_RET_STS_SUCCESS;
2238 If l_debug_level > 0 Then
2239 oe_debug_pub.add('Not able to get primary sold to exception');
2240 End If;
2241 Return;
2242 End;
2243
2244 l_tp_ret := EC_TRADING_PARTNER_PVT.Is_Entity_Enabled (
2245 p_api_version_number => 1.0
2246 ,p_init_msg_list => null
2247 ,p_simulate => null
2248 ,p_commit => null
2249 ,p_validation_level => null
2250 ,p_transaction_type => 'POAO'
2251 ,p_transaction_subtype => null
2252 ,p_entity_type => EC_TRADING_PARTNER_PVT.G_CUSTOMER
2253 ,p_entity_id => l_address_id
2254 ,p_return_status => l_tp_ret_status
2255 ,p_msg_count => l_msg_count
2256 ,p_msg_data => l_msg_data);
2257
2258 IF l_tp_ret = FALSE Then
2259 G_TP_RET := FALSE;
2260 x_return_status := FND_API.G_RET_STS_SUCCESS;
2261 If l_debug_level > 0 Then
2262 oe_debug_pub.add('Transaction not enabled');
2263 End If;
2264 -- Raise event to log message
2265 Return;
2266 End If;
2267 G_TP_RET := TRUE;
2268
2269 <<create_ack>>
2270
2271 -- Check if data has changed
2272 j := l_line_tbl.last;
2273 i := l_line_tbl.first;
2274
2275 While i Is Not Null Loop
2276 l_line_rec := l_line_tbl(I);
2277 oe_line_util.Convert_Miss_To_Null(p_x_line_rec =>l_line_rec);
2278 l_line_tbl(I) := l_line_rec;
2279
2280 -- bug 3439319 added this block instead of NULLing out the resv_qty
2281 Begin
2282 IF l_debug_level > 0 THEN
2283 oe_debug_pub.add('header_id => ' || l_line_tbl(I).header_id);
2284 oe_debug_pub.add('line_id => ' || l_line_tbl(I).line_id);
2285 oe_debug_pub.add('org_id => ' || l_line_tbl(I).org_id);
2286 END IF;
2287
2288 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_tbl(I).header_id);
2289
2290 IF l_debug_level > 0 THEN
2291 oe_debug_pub.add('l_sales_order_id => ' || l_sales_order_id);
2292 END IF;
2293
2294 l_line_tbl(I).reserved_quantity := oe_line_util.Get_Reserved_Quantity (
2295 p_header_id => l_sales_order_id,
2296 p_line_id => p_line_tbl(I).line_id,
2297 p_org_id => p_line_tbl(I).org_id);
2298 IF l_debug_level > 0 THEN
2299 oe_debug_pub.add('Reserved_Qty => ' || l_line_tbl(I).reserved_quantity);
2300 END IF;
2301
2302 Exception
2303 When Others Then
2304 l_line_tbl(I).reserved_quantity := NULL;
2305 END;
2306
2307 IF NOT (OE_GLOBALS.Equal(l_line_tbl(I).inventory_item_id,
2308 l_old_line_tbl(I).inventory_item_id)
2309 AND OE_GLOBALS.Equal(l_line_tbl(I).unit_selling_price,
2310 l_old_line_tbl(I).unit_selling_price)
2311 AND OE_GLOBALS.Equal(l_line_tbl(I).ordered_quantity,
2312 l_old_line_tbl(I).ordered_quantity)
2313 AND OE_GLOBALS.Equal(l_line_tbl(I).order_quantity_uom,
2314 l_old_line_tbl(I).order_quantity_uom)
2315 AND OE_GLOBALS.Equal(l_line_tbl(I).shipped_quantity,
2316 l_old_line_tbl(I).shipped_quantity)
2317 AND OE_GLOBALS.Equal(l_line_tbl(I).schedule_ship_date,
2318 l_old_line_tbl(I).schedule_ship_date)
2319 AND OE_GLOBALS.Equal(l_line_tbl(I).schedule_arrival_date,
2320 l_old_line_tbl(I).schedule_arrival_date)
2321 ) OR (
2322 l_old_line_tbl(I).operation = Oe_Globals.G_OPR_INSERT OR
2323 l_old_line_tbl(I).operation = Oe_Globals.G_OPR_CREATE
2324 )
2325 OR ( l_line_tbl(I).first_ack_code is null AND
2326 l_header_rec.first_ack_code is not null )--bug7207426
2327 Then
2328 l_ack_req_flag := 'B';
2329 If l_header_rec.first_ack_code is not null And
2330 l_line_tbl(I).FIRST_ACK_CODE Is Null Then
2331 l_line_tbl(I).FIRST_ACK_CODE := 'DR';
2332 l_line_tbl(I).FIRST_ACK_DATE := sysdate;
2333 l_old_line_tbl(I).FIRST_ACK_DATE := sysdate;
2334 End If;
2335
2336 Else
2337 If l_header_rec.first_ack_code Is Not Null Then
2338 l_line_tbl(I).changed_lines_pocao := 'N';
2339 End If;
2340 End If;
2341
2342 if (l_line_tbl(I).operation is NULL OR
2343 l_line_tbl(I).operation = FND_API.G_MISS_CHAR ) THEN
2344 l_line_tbl(I).operation := NULL; -- for bug 4764583/5178052
2345 oe_debug_pub.add(' Setting Operation G_MISS_CHAR to NULL');
2346 end if;
2347
2348 i := l_line_tbl.next(i);
2349
2350 End Loop;
2351
2352 If l_ack_req_flag = 'N' And
2353 l_header_rec_isnull = 'N' Then
2354 If NOT (OE_GLOBALS.Equal(l_header_rec.cust_po_number,
2355 l_old_header_rec.cust_po_number)
2356 And OE_GLOBALS.Equal(l_header_rec.ship_to_org_id,
2357 l_old_header_rec.ship_to_org_id)
2358 And OE_GLOBALS.Equal(l_header_rec.ordered_date,
2359 l_old_header_rec.ordered_date)) Then
2360 l_ack_req_flag := 'H';
2361 End If;
2362 End If;
2363
2364 If l_ack_req_flag In ('B','H') Or
2365 l_header_rec.first_ack_code Is Null Then
2366
2367 l_rejected_lines := 'Y';
2368 OE_Header_Util.Convert_Miss_To_Null (l_header_rec);
2369
2370 -- Start inserting the ack records
2371 OE_Header_Ack_Util.Insert_Row
2372 (p_header_rec => l_header_rec,
2373 x_ack_type => l_ack_type,
2374 x_return_status => l_return_status);
2375
2376 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2378 Elsif l_return_status = FND_API.G_RET_STS_ERROR Then
2379 RAISE FND_API.G_EXC_ERROR;
2380 Else
2381 l_raise_event := 'Y';
2382 End If;
2383 End If;
2384
2385 If l_ack_req_flag = 'B' Or
2386 l_header_rec.first_ack_code Is Null Then
2387 OE_Line_Ack_Util.Insert_Row
2388 (p_line_tbl => l_line_tbl,
2389 p_old_line_tbl => l_old_line_tbl,
2390 x_return_status => l_return_status);
2391 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2393 Elsif l_return_status = FND_API.G_RET_STS_ERROR Then
2394 RAISE FND_API.G_EXC_ERROR;
2395 Else
2396 l_raise_event := 'Y';
2397 End If;
2398
2399 -- do id to value conversion to get sold_to_org to pass when getting rejected lines
2400
2401 begin
2402 IF l_header_rec.sold_to_org_id is not null THEN
2403 OE_ID_TO_VALUE.sold_to_org(p_sold_to_org_id => l_header_rec.sold_to_org_id,
2404 x_org => l_sold_to_org,
2405 x_customer_number => l_customer_number);
2406 END IF;
2407
2408 exception
2409 when others then
2410 If l_debug_level > 0 Then
2411 oe_debug_pub.add('OTHERS EXCEPTION WHEN DERIVING SOLD TO ORG');
2412 End If;
2413 end;
2414
2415 OE_Rejected_Lines_Ack.Get_Rejected_Lines
2416 ( p_request_id => l_header_rec.request_id
2417 , p_order_source_id => l_header_rec.order_source_id
2418 , p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref
2419 , p_change_sequence => l_header_rec.change_sequence
2420 , x_rejected_line_tbl => l_reject_line_tbl
2421 , x_rejected_line_val_tbl => l_reject_line_val_tbl
2422 , x_rejected_lot_serial_tbl => l_reject_lot_serial_tbl
2423 , x_return_status => l_return_status
2424 , p_header_id => l_header_rec.header_id
2425 , p_sold_to_org => l_sold_to_org
2426 , p_sold_to_org_id => l_header_rec.sold_to_org_id
2427 );
2428
2429 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2431 Elsif l_return_status = FND_API.G_RET_STS_ERROR Then
2432 RAISE FND_API.G_EXC_ERROR;
2433 End If;
2434
2435 If l_reject_line_tbl.COUNT > 0 Then
2436 OE_Line_Ack_Util.Insert_Row
2437 (p_line_tbl => l_reject_line_tbl
2438 ,p_old_line_tbl => l_reject_line_tbl
2439 ,p_line_val_tbl => l_reject_line_val_tbl
2440 ,p_old_line_val_tbl => l_reject_line_val_tbl
2441 ,p_buyer_seller_flag => 'B'
2442 ,p_reject_order => 'Y'
2443 ,x_return_status => l_return_status
2444 );
2445
2446 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2448 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2449 RAISE FND_API.G_EXC_ERROR;
2450 Else
2451 l_raise_event := 'Y';
2452 END IF;
2453
2454 End If;
2455
2456 End If;
2457
2458 -- Raise the event (Pack J)
2459 If l_raise_event = 'Y' Then
2460 OE_Update_Ack_Util.Raise_Derive_Ack_Data_event
2461 (p_transaction_type => 'ONT',
2462 p_header_id => l_header_rec.header_id,
2463 p_org_id => l_header_rec.org_id,
2464 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
2465 p_change_sequence => l_header_rec.change_sequence,
2466 p_sold_to_org_id => l_header_rec.sold_to_org_id,
2467 p_order_number => l_header_rec.order_number,
2468 p_order_source_id => l_header_rec.order_source_id,
2469 p_transaction_subtype => l_ack_type,
2470 p_order_type_id => l_header_rec.order_type_id,
2471 p_xml_msg_id => l_header_rec.xml_message_id, --l_xml_message_id,
2472 x_return_status => l_return_status);
2473 End If;
2474
2475 x_return_status := FND_API.G_RET_STS_SUCCESS;
2476
2477 Exception
2478 When Others Then
2479 If l_debug_level >0 Then
2480 Oe_Debug_Pub.Add('When Others in new Process_Acknowledgment');
2481 Oe_Debug_Pub.Add('Error: '||sqlerrm);
2482 End If;
2483
2484 End Process_Acknowledgment;
2485
2486
2487
2488 END OE_Acknowledgment_Pvt;