[Home] [Help]
PACKAGE BODY: APPS.OE_SYNC_ORDER_PVT
Source
1 PACKAGE BODY OE_SYNC_ORDER_PVT AS
2 /* $Header: OEXVGNOB.pls 120.18.12020000.2 2013/02/06 09:59:37 sahvivek ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_SYNC_ORDER_PVT';
5
6
7 --
8 -- Start : Added for Enh. 7244277 (To solve bug 7622467)
9 --
10 -- Procedure to get customer details based on site use ID and site use code.
11 --
12 PROCEDURE get_customer_details
13 (
14 p_site_use_id IN NUMBER,
15 p_site_use_code IN VARCHAR2,
16 x_customer_id OUT NOCOPY NUMBER,
17 x_customer_name OUT NOCOPY VARCHAR2,
18 x_customer_number OUT NOCOPY VARCHAR2,
19 x_customer_site_id OUT NOCOPY NUMBER,
20 x_party_site_id OUT NOCOPY NUMBER
21 ) IS
22 --
23 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
24 --
25 BEGIN
26 IF l_debug_level > 0 THEN
27 oe_debug_pub.add('Entering get_customer_details: ', 5);
28 oe_debug_pub.add(' p_site_use_id = ' || p_site_use_id, 5);
29 oe_debug_pub.add(' p_site_use_code = ' || p_site_use_code, 5);
30 END IF;
31
32 SELECT cust.cust_account_id,
33 party.party_name,
34 cust.account_number,
35 site.cust_acct_site_id,
36 cas.party_site_id
37 INTO x_customer_id,
38 x_customer_name,
39 x_customer_number,
40 x_customer_site_id,
41 x_party_site_id
42 FROM
43 hz_cust_site_uses site,
44 hz_cust_acct_sites cas,
45 hz_cust_accounts cust,
46 hz_parties party
47 WHERE site.site_use_code = p_site_use_code
48 AND site_use_id = p_site_use_id
49 AND site.cust_acct_site_id = cas.cust_acct_site_id
50 AND cas.cust_account_id = cust.cust_account_id
51 AND cust.party_id=party.party_id;
52
53 IF l_debug_level > 0 THEN
54 oe_debug_pub.add(' Calculated customer_id: ' || x_customer_id, 5);
55 oe_debug_pub.add(' Calculated customer_name: ' || x_customer_name, 5);
56 oe_debug_pub.add(' Calculated customer_number: ' || x_customer_number, 5);
57 oe_debug_pub.add(' Calculated customer_site_id: ' || x_customer_site_id, 5);
58 oe_debug_pub.add(' Calculated party_site_id: ' || x_party_site_id, 5);
59 oe_debug_pub.add('Exiting get_customer_details...', 5);
60 END IF;
61
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 oe_debug_pub.add(' GetCustomerDetails::: No Data Found: ' || sqlerrm);
65
66 When too_many_rows then
67 oe_debug_pub.add(' GetCustomerDetails::: Too Many Rows: ' || sqlerrm);
68
69 When others then
70 oe_debug_pub.add(' GetCustomerDetails::: Error occurred: ' || sqlerrm);
71
72 END get_customer_details;
73
74 -- This function retrieves the org_contact_id from the party layer,
75 -- given the cust_acct_role_id from the customer layer.
76 FUNCTION get_party_org_contact_id
77 (
78 p_cust_acct_role_id IN number
79 )
80 RETURN NUMBER
81 IS
82 l_org_contact_id NUMBER;
83 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
84 BEGIN
85
86 IF l_debug_level > 0 THEN
87 oe_debug_pub.add(' in get_party_org_contact_id: ' ||
88 p_cust_acct_role_id || '...',5);
89 END IF;
90
91 IF ( p_cust_acct_role_id IS NULL ) THEN
92 RETURN NULL;
93 END IF;
94
95 SELECT org_contact_id
96 INTO l_org_contact_id
97 FROM hz_org_contacts oc,
98 hz_cust_account_roles car,
99 hz_relationships r
100 WHERE r.party_id = car.party_id
101 AND r.relationship_id = oc.party_relationship_id
102 AND cust_account_role_id = p_cust_acct_role_id
103 AND r.directional_flag = 'F'
104 ;
105
106 IF l_debug_level > 0 THEN
107 oe_debug_pub.add(' l_org_contact_id: ' ||
108 l_org_contact_id || '...',5);
109 END IF;
110
111 RETURN l_org_contact_id;
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF l_debug_level > 0 THEN
115 oe_debug_pub.add(' Returning null from exception block... ',5);
116 END IF;
117 RETURN NULL;
118 END;
119
120 -- End : Added for Enh. 7244277 (solving bug 7622467)
121
122
123 PROCEDURE INSERT_SYNC_HEADER
124 (
125 P_HEADER_REC OE_Order_PUB.Header_Rec_Type,
126 P_CHANGE_TYPE VARCHAR2,
127 p_req_id NUMBER,
128 X_RETURN_STATUS OUT NOCOPY VARCHAR2
129
130 )
131 IS
132 l_itemkey NUMBER;
133 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
134
135 -- Start: Added for Enh. 7244277 (solving bug 7622467)
136 l_customer_id number;
137 l_customer_name varchar2(256);
138 l_customer_number varchar2(256);
139 l_bill_to_cust_site_id number;
140 l_bill_to_party_site_id number;
141 -- End : Added for Enh. 7244277 (solving bug 7622467)
142 BEGIN
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 IF l_debug_level > 0 THEN
146 oe_debug_pub.add( ' ENTERING OE_SYNC_ORDER_PVT.INSERT_SYNC_HEADER :'||
147 'header id :'||P_HEADER_REC.HEADER_ID || ' p_change type :'||P_CHANGE_TYPE);
148 END IF;
149
150 -- Retrieve the value: invoice_to_customer_id. (Bug # 7622467)
151 IF ( p_header_rec.invoice_to_org_id IS NOT NULL ) THEN
152 IF l_debug_level > 0 THEN
153 oe_debug_pub.add(' p_header_rec.invoice_to_org_id... ' ||
154 p_header_rec.invoice_to_org_id, 5);
155 END IF;
156
157 get_customer_details
158 (
159 p_site_use_id => p_header_rec.invoice_to_org_id,
160 p_site_use_code => 'BILL_TO',
161 x_customer_id => l_customer_id,
162 x_customer_name => l_customer_name,
163 x_customer_number => l_customer_number,
164 x_customer_site_id => l_bill_to_cust_site_id,
165 x_party_site_id => l_bill_to_party_site_id
166 );
167
168 END IF;
169
170 INSERT INTO oe_header_acks
171 (header_id
172 ,acknowledgment_type
173 ,last_ack_code
174 ,request_id
175 ,sold_to_org_id
176 ,change_sequence
177 ,flow_status_code
178 ,orig_sys_document_ref
179 ,order_number
180 ,ordered_date
181 ,org_id
182 ,order_source_id
183 -- Start: Added for Enh. 7244277
184 ,invoice_address_id -- Bug # 7622467
185 ,price_list_id -- Bug # 7644412
186 -- End : Added for Enh. 7244277
187 ,creation_date
188 ,transactional_curr_code) -- 9182921
189 VALUES (
190 P_HEADER_REC.header_id
191 ,'SEBL_SYNC'
192 ,P_HEADER_REC.flow_status_code
193 ,p_req_id
194 ,p_header_rec.sold_to_org_id
195 ,p_header_rec.change_sequence
196 ,decode(p_change_type, 'APPLY', 'ON_HOLD',
197 'RELEASE', 'RELEASED',
198 p_header_rec.flow_status_code)
199 ,p_header_rec.orig_sys_document_ref
200 ,p_header_rec.order_number
201 ,p_header_rec.ordered_date
202 ,p_header_rec.org_id
203 ,p_header_rec.order_source_id
204 -- Start: Added for Enh. 7244277
205 ,l_bill_to_party_site_id
206 ,p_header_rec.price_list_id
207 -- End : Added for Enh. 7244277
208 ,sysdate
209 ,p_header_rec.transactional_curr_code); -- Bug 9182921
210
211 IF l_debug_level > 0 THEN
212 oe_debug_pub.add( ' EXITING OE_SYNC_ORDER_PVT.INSERT_SYNC_HEADER');
213 END IF;
214
215 EXCEPTION
216
217 WHEN FND_API.G_EXC_ERROR THEN
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 -- Get message count and data
220 IF l_debug_level > 0 THEN
221 oe_debug_pub.add( ' OE_SYNC_ORDER_PVT -G_EXC_ERROR');
222 END IF;
223
224 WHEN OTHERS THEN
225
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227 IF l_debug_level > 0 THEN
228 oe_debug_pub.add( ' OE_SYNC_ORDER_PVT - G_RET_STS_UNEXP_ERROR');
229 END IF;
230
231 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
232 THEN
233 OE_MSG_PUB.Add_Exc_Msg
234 ( G_PKG_NAME
235 , 'OE_SYNC_ORDER_PVT.INSERT_SYNC_HEADER'
236 );
237 END IF;
238 END;
239
240 PROCEDURE INSERT_SYNC_LINE
241 (
242 p_line_rec oe_order_pub.line_rec_type,
243 p_change_type varchar2,
244 p_req_id number,
245 x_return_status out NOCOPY varchar2
246 )
247 IS
248 l_itemkey number;
249 l_parent_rec oe_order_pub.line_rec_type;
250 l_tmp_flow_status_code varchar2(256);
251 l_count number;
252 l_debug_level CONSTANT number := oe_debug_pub.g_debug_level;
253
254 -- Customer Information
255 o_ship_to_cust_id number;
256 o_ship_to_cust_site_id number;
257 o_ship_to_party_site_id number;
258 o_bill_to_cust_id number;
259 o_bill_to_cust_site_id number;
260 o_bill_to_party_site_id number;
261 o_cust_name varchar2(256);
262 o_cust_num VARCHAR2(30); -- Fix for bug 10017782
263
264 o_ship_to_prty_cntct_id number;
265 o_bill_to_prty_cntct_id number;
266 BEGIN
267
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269
270 IF l_debug_level > 0 THEN
271 oe_debug_pub.add( ' ENTERING OE_SYNC_ORDER_PVT.INSERT_SYNC_LINE :'||
272 'line id :'||P_LINE_REC.LINE_ID || ' p_change type :'||P_CHANGE_TYPE||
273 'flow status: ' || p_line_rec.flow_status_code);
274 END IF;
275
276 IF l_debug_level > 0 THEN
277 oe_debug_pub.add( ' l_itemkey' || l_itemkey);
278 oe_debug_pub.add( ' itemtypecode' || p_line_rec.item_type_code);
279 oe_debug_pub.add( ' split from line' || p_line_rec.split_from_line_id);
280 END IF;
281
282 IF p_line_rec.item_type_code='CONFIG' and p_line_rec.split_from_line_id IS NOT NULL THEN
283 oe_line_util.query_row(
284 p_line_id => p_line_rec.top_model_line_id
285 ,x_line_rec => l_parent_rec
286 );
287 oe_debug_pub.add( 'top model split from line' || l_parent_rec.split_from_line_id);
288 END IF;
289
290 -- Retrieve the ORG_CONTACT_ID based on
291 -- p_line_rec.[invoice ship]_to_contact_id
292 o_ship_to_prty_cntct_id := get_party_org_contact_id(
293 p_line_rec.ship_to_contact_id
294 );
295 o_bill_to_prty_cntct_id := get_party_org_contact_id(
296 p_line_rec.invoice_to_contact_id
297 );
298
299 -- Retrieve the value: invoice_to_customer_id
300 IF ( p_line_rec.invoice_to_org_id IS NOT NULL ) THEN
301 IF l_debug_level > 0 THEN
302 oe_debug_pub.add( 'p_line_rec.invoice_to_org_id: ' ||
303 p_line_rec.invoice_to_org_id, 5);
304 END IF;
305 get_customer_details
306 (
307 p_site_use_id => p_line_rec.invoice_to_org_id,
308 p_site_use_code => 'BILL_TO',
309 x_customer_id => o_bill_to_cust_id,
310 x_customer_name => o_cust_name,
311 x_customer_number => o_cust_num,
312 x_customer_site_id => o_bill_to_cust_site_id,
313 x_party_site_id => o_bill_to_party_site_id
314 );
315 IF l_debug_level > 0 THEN
316 oe_debug_pub.add( 'Done with p_line_rec.invoice_to_org_id.', 5);
317 END IF;
318 END IF;
319
320 -- Retrieve the value: ship_to_customer_id
321 IF ( p_line_rec.ship_to_org_id IS NOT NULL ) THEN
322 IF l_debug_level > 0 THEN
323 oe_debug_pub.add( 'p_line_rec.ship_to_org_id: ' ||
324 p_line_rec.ship_to_org_id, 5);
325 END IF;
326
327 get_customer_details
328 (
329 p_site_use_id => p_line_rec.ship_to_org_id,
330 p_site_use_code => 'SHIP_TO',
331 x_customer_id => o_ship_to_cust_id,
332 x_customer_name => o_cust_name,
333 x_customer_number => o_cust_num,
334 x_customer_site_id => o_ship_to_cust_site_id,
335 x_party_site_id => o_ship_to_party_site_id
336 );
337
338 IF l_debug_level > 0 THEN
339 oe_debug_pub.add( 'Done with p_line_rec.ship_to_org_id.', 5);
340 END IF;
341 END IF;
342
343 IF l_debug_level > 0 THEN
344 oe_debug_pub.add( 'Inserting into line acks.', 5);
345 END IF;
346
347 INSERT INTO oe_line_acks
348 (header_id
349 ,line_id
350 ,acknowledgment_type
351 ,last_ack_code
352 ,request_id
353 ,change_sequence
354 ,flow_status_code
355 ,ordered_quantity
356 ,schedule_arrival_date
357 ,schedule_ship_date
358 ,config_header_id
359 ,config_rev_nbr
360 ,configuration_id
361 ,orig_sys_document_ref
362 ,orig_sys_line_ref
363 ,orig_sys_shipment_ref
364 ,split_from_line_id
365 ,inventory_item_id
366 ,org_id
367 ,order_source_id
368 ,order_quantity_uom
369 ,top_model_line_id
370 ,item_type_code
371 -- Start: enh. 7244277
372 ,line_number
373 ,tax_value
374 ,agreement_id
375 ,payment_term_id
376 ,promise_date
377 ,shipping_method_code
378 ,shipment_priority_code
379 ,freight_terms_code
380 ,ship_to_customer_id
381 ,ship_to_contact_id
382 ,ship_to_org_id
383 ,invoice_to_customer_id
384 ,invoice_to_contact_id
385 ,invoice_to_org_id
386 ,unit_selling_price -- 7644412
387 ,price_list_id -- 7644412
388 ,unit_list_price -- 7644412
389 ,unit_list_price_per_pqty -- 7644412
390 ,unit_percent_base_price -- 7644412
391 ,unit_selling_price_per_pqty -- 7644412
392 ,pricing_date -- 7644412
393 ,ship_to_address_id
394 -- End : enh. 7244277
395 ,creation_date
396 -- O2C25
397 ,ship_from_org_id
398 ,ship_from_org
399 ,ship_to_org
400 ,invoice_to_org
401 ,line_category_code) -- 9151484
402 VALUES(
403 p_line_rec.header_id
404 ,decode(p_line_rec.item_type_code,'CONFIG',p_line_rec.top_model_line_id,p_line_rec.line_id)
405 ,'SEBL_SYNC'
406 ,p_line_rec.flow_status_code
407 ,p_req_id
408 ,p_line_rec.change_sequence
409 ,decode(p_change_type, 'APPLY', 'ON_HOLD',
410 'RELEASE', 'RELEASED',
411 p_line_rec.flow_status_code
412 )
413 ,p_line_rec.ordered_quantity
414 ,p_line_rec.schedule_arrival_date
415 ,p_line_rec.schedule_ship_date
416 ,p_line_rec.config_header_id
417 ,p_line_rec.config_rev_nbr
418 ,p_line_rec.configuration_id
419 ,p_line_rec.orig_sys_document_ref
420 ,p_line_rec.orig_sys_line_ref
421 ,p_line_rec.orig_sys_shipment_ref
422 ,decode(p_line_rec.item_type_code,'CONFIG',l_parent_rec.split_from_line_id,p_line_rec.split_from_line_id)
423 ,p_line_rec.inventory_item_id
424 ,p_line_rec.org_id
425 ,p_line_rec.order_source_id
426 ,p_line_rec.order_quantity_uom
427 ,p_line_rec.top_model_line_id
428 ,p_line_rec.item_type_code
429 -- Start : Enh. 7244277
430 ,p_line_rec.line_number
431 -- ,Decode(p_line_rec.line_category_code,'RETURN',-p_line_rec.tax_value, p_line_rec.tax_value) -- Bug 8977354
432 ,p_line_rec.tax_value
433 ,p_line_rec.agreement_id
434 ,p_line_rec.payment_term_id
435 ,p_line_rec.promise_date
436 ,p_line_rec.shipping_method_code
437 ,p_line_rec.shipment_priority_code
438 ,p_line_rec.freight_terms_code
439 ,o_ship_to_cust_id
440 ,o_ship_to_prty_cntct_id
441 ,p_line_rec.ship_to_org_id
442 ,o_bill_to_cust_id
443 ,o_bill_to_prty_cntct_id
444 ,p_line_rec.invoice_to_org_id
445 ,p_line_rec.unit_selling_price
446 ,p_line_rec.price_list_id
447 ,p_line_rec.unit_list_price
448 ,p_line_rec.unit_list_price_per_pqty
449 ,p_line_rec.unit_percent_base_price
450 ,p_line_rec.unit_selling_price_per_pqty
451 ,p_line_rec.pricing_date
452 ,o_ship_to_party_site_id
453 -- End : enh. 7244277
454 ,SYSDATE
455 -- O2C25
456 ,p_line_rec.ship_from_org_id
457 ,Oe_Genesis_Util.Inventory_Org(p_line_rec.ship_from_org_id)
458 ,Oe_Genesis_Util.Inventory_Org(p_line_rec.ship_to_org_id)
459 ,Oe_Genesis_Util.Inventory_Org(p_line_rec.invoice_to_org_id)
460 ,p_line_rec.line_category_code -- 9151484
461 );
462
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add( ' EXITING OE_SYNC_ORDER_PVT.INSERT_SYNC_LINE');
465 END IF;
466
467 EXCEPTION
468
469 WHEN FND_API.G_EXC_ERROR THEN
470 x_return_status := FND_API.G_RET_STS_ERROR;
471 -- Get message count and data
472
473 IF l_debug_level > 0 THEN
474 oe_debug_pub.add( ' OE_SYNC_ORDER_PVT -G_EXC_ERROR');
475 END IF;
476 WHEN OTHERS THEN
477
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
479
480 IF l_debug_level > 0 THEN
481 oe_debug_pub.add( ' OE_SYNC_ORDER_PVT - G_RET_STS_UNEXP_ERROR');
482 END IF;
483 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
484 THEN
485 OE_MSG_PUB.Add_Exc_Msg
486 ( G_PKG_NAME
487 , 'OE_SYNC_ORDER_PVT.INSERT_SYNC_LINE'
488 );
489 END IF;
490
491 END;
492
493 PROCEDURE sync_header_line(p_header_rec IN OE_Order_Pub.Header_Rec_Type
494 ,p_line_rec IN OE_Order_PUB.Line_Rec_Type
495 ,p_hdr_req_id IN NUMBER DEFAULT NULL
496 ,p_lin_req_id IN NUMBER DEFAULT NULL
497 ,p_change_type IN VARCHAR2 DEFAULT NULL
498 ,p_hold_source_id IN NUMBER DEFAULT NULL
499 ,p_order_hold_id IN NUMBER DEFAULT NULL
500 ,p_hold_release_id IN NUMBER DEFAULT NULL) IS
501
502 CURSOR get_hdr_for_hldsrc_cur IS
503 SELECT ohdr.header_id
504 , ohld.line_id
505 ,'SEBL_SYNC'
506 ,ohdr.flow_status_code
507 ,ohdr.request_id
508 ,ohdr.sold_to_org_id
509 ,ohdr.change_sequence
510 ,ohdr.orig_sys_document_ref
511 ,ohdr.order_number
512 ,ohdr.ordered_date
513 ,ohdr.org_id
514 ,ohdr.order_source_id
515 ,ohld.released_flag
516 FROM oe_order_headers_all ohdr,
517 oe_order_holds_all ohld,
518 oe_order_sources osrc -- to remove hardcoding on order_source_id
519 WHERE ohdr.header_id = ohld.header_id
520 -- AND ohdr.order_source_id = 28
521 AND ohdr.order_source_id = osrc.order_source_id
522 and osrc.aia_enabled_flag = 'Y'
523 AND ohld.hold_source_id = p_hold_source_id
524 AND decode(p_change_type,'RELEASE',ohld.hold_release_id,-99)
525 = decode(p_change_type,'RELEASE',p_hold_release_id,-99)
526 -- AND ohdr.booked_flag = 'Y' bug 16041842
527 -- AND flow_status_code <> 'ENTERED' bug 16041842
528 ORDER BY ohld.header_id;
529
530 CURSOR get_ord_hld_cur IS
531 SELECT ooh.header_id
532 ,ooh.line_id
533 FROM oe_order_holds ooh,
534 oe_order_headers_all h,
535 oe_order_sources osrc -- to remove hardcoding on order_source_id
536 WHERE h.header_id = ooh.header_id
537 -- AND h.order_source_id = 28
538 AND h.order_source_id = osrc.order_source_id
539 AND osrc.aia_enabled_flag = 'Y'
540 AND ooh.order_hold_id = p_order_hold_id;
541
542
543 l_line_rec OE_Order_PUB.Line_Rec_Type;
544 l_header_rec OE_Order_PUB.Header_Rec_Type;
545 l_prev_header_id NUMBER;
546
547 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
548 l_itemkey number;
549 l_return_status VARCHAR2(1);
550
551 BEGIN
552 IF l_debug_level > 0 THEN
553 oe_debug_pub.add('OE_sync_order_PVT - Entering sync_header_line');
554 oe_debug_pub.add('sync_header_line - p_header_id : '||p_header_rec.header_id);
555 oe_debug_pub.add('sync_header_line - booked_flag : '||p_header_rec.booked_flag);
556 oe_debug_pub.add('sync_header_line - p_line_id : '||p_line_rec.line_id);
557 oe_debug_pub.add('sync_header_line - p_hdr_req_id : '||p_hdr_req_id);
558 oe_debug_pub.add('sync_header_line - p_lin_req_id : '||p_lin_req_id);
559 oe_debug_pub.add('sync_header_line - p_change_type : '||p_change_type);
560 oe_debug_pub.add('sync_header_line - p_hold_source_id: '||p_hold_source_id);
561 oe_debug_pub.add('sync_header_line - p_order_hold_id : '||p_order_hold_id);
562 oe_debug_pub.add('sync_header_line - p_hold_release_id : '||p_hold_release_id);
563 END IF;
564
565 ----
566 -- Outer IF introduced during the fix of 10236368. When hold source based
567 -- application/release of holds happens, input parameters to sync_header_line(...)
568 -- procedure are just p_change_type, p_hold_source_id and the other parameters
569 -- are set to NULL. Given that, processing has been halting right at the very
570 -- first check i.e., source_aia_enabled(p_header_rec.order_source_id). In that
571 -- failure scenario, the control was getting returned back then and there,
572 -- resulting in no business event raise.
573 --
574 -- When the hold application/release for one order/line is being performed,
575 -- sync_header_line(...) receives the full p_header_rec, and the p_line_rec
576 -- as applicable. In this case source_aia_enabled(...) performs the task
577 -- expected of it, without any problems (as already observed by the customer
578 -- before they filed bug 10236368).
579 --
580 -- The proposed fix here for 10236368: defer checking whether order/line
581 -- belongs to an AIA enabled order source until later, when processing
582 -- hold application/release.
583 ----
584 IF p_change_type NOT IN ('APPLY', 'RELEASE') THEN
585 -- Fix of bug 8205201
586 IF ( NOT oe_genesis_util.source_aia_enabled(p_header_rec.order_source_id) )
587 THEN
588 oe_debug_pub.ADD('sync_header_line - Order # ' || p_header_rec.order_number
589 || ', ' || ' order source id: ' || p_header_rec.order_source_id);
590 oe_debug_pub.ADD('sync_header_line - not processing non-AIA order/quote...');
591 RETURN;
592 ELSE
593 oe_debug_pub.ADD('sync_header_line - p_header_rec.order_source_id: ' ||
594 p_header_rec.order_source_id);
595 oe_debug_pub.ADD('sync_header_line - processing synch operation on AIA...');
596 END IF; -- End of fix of bug 8205201
597 END IF;
598 ----
599 -- 10236368
600 ----
601
602 IF p_change_type IN ('APPLY', 'RELEASE') THEN
603
604 IF (
605 p_line_rec.line_id IS NOT NULL AND
606 -- p_header_rec.booked_flag = 'Y' AND bug 16041842
607 oe_genesis_util.source_aia_enabled(p_line_rec.order_source_id) AND -- 10236368
608 oe_genesis_util.source_aia_enabled(p_header_rec.order_source_id) -- 10236368
609 ) THEN
610
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add('sync_header_line- APPLY- Inserting row into oe_line_acks for p_line_id');
613 END IF;
614
615 select OE_XML_MESSAGE_SEQ_S.nextval
616 into l_itemkey
617 from dual;
618
619 INSERT_SYNC_lINE(P_LINE_rec => P_LINE_rec,
620 p_change_type => p_change_type,
621 p_req_id => l_itemkey, -- XXXX
622 X_RETURN_STATUS => L_RETURN_STATUS);
623
624 IF l_debug_level > 0 THEN
625 oe_debug_pub.add('sync_header_line -APPLY- Inserting into oe_line_acks for p_line_id is DONE'||l_return_status);
626 END IF;
627
628 IF p_header_rec.header_id IS NOT NULL THEN
629 IF l_debug_level > 0 THEN
630 oe_debug_pub.add(' INSERT_SYNC_HEADER - inserting for apply holds ');
631 END IF;
632
633 INSERT_SYNC_HEADER(p_header_rec => p_header_rec,
634 p_change_type => null, --TODO
635 p_req_id => l_itemkey, --XXXX
636 x_return_status => l_return_status);
637 END IF;
638 IF l_debug_level > 0 THEN
639 oe_debug_pub.add('sync_header_line - APPLY-inserted into line acks');
640 END IF;
641
642 IF l_debug_level > 0 THEN
643 oe_debug_pub.add('sync_header_line - Raise BPEL event by calling raise_bpel_out_event1');
644 END IF;
645
646 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
647 raise_bpel_out_event(p_header_id => p_header_rec.header_id
648 ,p_line_id => p_line_rec.line_id
649 ,p_hdr_req_id => l_itemkey --XXXX
650 ,p_lin_req_id => l_itemkey --XXXX
651 ,p_change_type => p_change_type
652 ,p_hold_source_id => p_hold_source_id
653 ,p_order_hold_id => p_order_hold_id);
654 END IF;
655 ELSIF p_line_rec.line_id IS NULL AND
656 p_header_rec.header_id IS NOT NULL AND
657 -- p_header_rec.booked_flag = 'Y' AND bug 16041842
658 oe_genesis_util.source_aia_enabled(p_header_rec.order_source_id) THEN -- 10236368
659
660 IF l_debug_level > 0 THEN
661 oe_debug_pub.add('sync_header_line - Inserting row into oe_header_acks for header_id');
662 END IF;
663 select OE_XML_MESSAGE_SEQ_S.nextval
664 into l_itemkey
665 from dual;
666
667 INSERT_SYNC_HEADER(p_header_rec => p_header_rec,
668 p_change_type => p_change_type,
669 p_req_id => l_itemkey, --XXXX
670 x_return_status => l_return_status);
671
672 IF l_debug_level > 0 THEN
673 oe_debug_pub.add('sync_header_line - Inserting into oe_header_acks for header_id is DONE');
674 END IF;
675
676 IF l_debug_level > 0 THEN
677 oe_debug_pub.add('sync_header_line - Raise BPEL event by calling raise_bpel_out_event2');
678 END IF;
679
680 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
681 raise_bpel_out_event(p_header_id => p_header_rec.header_id
682 ,p_line_id => p_line_rec.line_id
683 ,p_hdr_req_id => l_itemkey --XXXX
684 ,p_lin_req_id => l_itemkey --XXXX
685 ,p_change_type => p_change_type
686 ,p_hold_source_id => p_hold_source_id
687 ,p_order_hold_id => p_order_hold_id);
688 END IF;
689
690 ELSIF p_line_rec.line_id IS NULL AND
691 p_header_rec.header_id IS NULL AND
692 p_hold_source_id IS NOT NULL THEN
693 ----
694 --
695 -- Comments added during resolution of 10236368
696 --
697 -- In this case, the cursor 'get_hdr_for_hldsrc_cur' itself will determine all the
698 -- order headers affected by the hold source (application/release); that cursor itself
699 -- is sensitive to the order header being AIA enabled or not.
700 ----
701
702 IF l_debug_level > 0 THEN
703 oe_debug_pub.add('sync_header_line - Inserting row into oe_header_acks for p_hold_source_id');
704 END IF;
705
706
707 FOR hdr_rec IN get_hdr_for_hldsrc_cur
708 LOOP
709
710 BEGIN
711 l_return_status := FND_API.G_RET_STS_SUCCESS;
712
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add('sync_header_line- in loop -released flag'||hdr_rec.released_flag);
715 END IF;
716 IF l_debug_level > 0 THEN
717 oe_debug_pub.add('sync_header_line- hdr_rec.line_id: '||hdr_rec.line_id);
718 END IF;
719
720
721 IF hdr_rec.line_id IS NULL THEN
722
723 select OE_XML_MESSAGE_SEQ_S.nextval
724 into l_itemkey
725 from dual;
726
727 IF l_debug_level > 0 THEN
728 oe_debug_pub.add('sync_header_line - l_itemkey '|| l_itemkey);
729 END IF;
730
731 INSERT INTO oe_header_acks
732 (header_id
733 ,acknowledgment_type
734 ,last_ack_code
735 ,request_id
736 ,sold_to_org_id
737 ,change_sequence
738 ,flow_status_code
739 ,orig_sys_document_ref
740 ,order_number
741 ,ordered_date
742 ,org_id
743 ,order_source_id)
744 VALUES
745 (hdr_rec.header_id
746 ,'SEBL_SYNC'
747 ,hdr_rec.flow_status_code
748 ,l_itemkey
749 ,hdr_rec.sold_to_org_id
750 ,hdr_rec.change_sequence
751 ,decode(p_change_type, 'APPLY', 'ON_HOLD',
752 'RELEASE', 'RELEASED',
753 hdr_rec.flow_status_code)
754 ,hdr_rec.orig_sys_document_ref
755 ,hdr_rec.order_number
756 ,hdr_rec.ordered_date
757 ,hdr_rec.org_id
758 ,hdr_rec.order_source_id);
759
760 IF l_debug_level > 0 THEN
761 oe_debug_pub.add('sync_header_line - after insert');
762 END IF;
763 ELSE -- hdr_rec.line_id is NOT NULL
764 IF l_debug_level > 0 THEN
765 oe_debug_pub.add('sync_header_line - hdr_rec.header_id'||hdr_rec.header_id);
766 oe_debug_pub.add('sync_header_line - l_prev_header_id'||l_prev_header_id);
767 END IF;
768 If hdr_rec.header_id <> nvl(l_prev_header_id,-1) THEN
769 select OE_XML_MESSAGE_SEQ_S.nextval
770 into l_itemkey
771 from dual;
772
773 IF l_debug_level > 0 THEN
774 oe_debug_pub.add('sync_header_line-l_prev_header_id=' || l_prev_header_id);
775 oe_debug_pub.add('sync_header_line-hdr_rec.header_id=' || hdr_rec.header_id);
776 oe_debug_pub.add('sync_header_line-l_item_key=' || l_itemkey );
777 END IF;
778 INSERT INTO oe_header_acks
779 (header_id
780 ,acknowledgment_type
781 ,last_ack_code
782 ,request_id
783 ,sold_to_org_id
784 ,change_sequence
785 ,flow_status_code
786 ,orig_sys_document_ref
787 ,order_number
788 ,ordered_date
789 ,org_id
790 ,order_source_id)
791 VALUES
792 (hdr_rec.header_id
793 ,'SEBL_SYNC'
794 ,hdr_rec.flow_status_code
795 ,l_itemkey
796 ,hdr_rec.sold_to_org_id
797 ,hdr_rec.change_sequence
798 ,hdr_rec.flow_status_code
799 ,hdr_rec.orig_sys_document_ref
800 ,hdr_rec.order_number
801 ,hdr_rec.ordered_date
802 ,hdr_rec.org_id
803 ,hdr_rec.order_source_id);
804
805 l_prev_header_id := hdr_rec.header_id;
806 END IF;
807
808 IF l_debug_level > 0 THEN
809 oe_debug_pub.add('sync_header_line - calling query row ');
810 END IF;
811 oe_line_util.query_row(
812 p_line_id => hdr_rec.line_id
813 ,x_line_rec => l_line_rec
814 );
815 INSERT_SYNC_lINE(P_LINE_REC => L_LINE_REC,
816 p_change_type => p_change_type,
817 p_req_id => l_itemkey, --XXXX
818 X_RETURN_STATUS => L_RETURN_STATUS);
819 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
820 IF l_debug_level > 0 THEN
821 oe_debug_pub.add('sync_header_line - Line inserted');
822 END IF;
823 END IF;
824
825 END IF; -- hdr_rec.line_id
826
827 IF l_debug_level > 0 THEN
828 oe_debug_pub.add('sync_header_line - Raise BPEL event by calling raise_bpel_out_event3');
829 END IF;
830
831 ---
832 -- Would be good to do a check on l_return_status before calling this API.
833 -- However, when hdr_rec.line_id is NULL, we will not have a reliable
834 -- value for that variable. Skipping such a check temporarily.
835 ---
836 raise_bpel_out_event(
837 p_header_id => hdr_rec.header_id
838 ,p_line_id => hdr_rec.line_id
839 ,p_hdr_req_id => l_itemkey --XXXX
840 ,p_lin_req_id => l_itemkey --XXXX
841 ,p_change_type => p_change_type
842 ,p_hold_source_id => p_hold_source_id
843 ,p_order_hold_id => p_order_hold_id);
844
845 EXCEPTION
846 WHEN FND_API.G_EXC_ERROR THEN
847 IF l_debug_level > 0 THEN
848 oe_debug_pub.add('sync_header_line - G_EXC_ERROR Inside Main LOOP for Header/LineID: '
849 || hdr_rec.header_id || '/' || hdr_rec.line_id);
850 END IF;
851 l_return_status := FND_API.G_RET_STS_ERROR;
852
853 WHEN OTHERS THEN
854 IF l_debug_level > 0 THEN
855 oe_debug_pub.add('sync_header_line - OTHERS Inside Main LOOP for Header/LineID: '
856 || hdr_rec.header_id || '/' || hdr_rec.line_id);
857 END IF;
858 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
859 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
860 THEN
861 OE_MSG_PUB.Add_Exc_Msg
862 ( G_PKG_NAME
863 ,'OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE'
864 );
865 END IF;
866 END;
867
868 END LOOP;
869
870 IF l_debug_level > 0 THEN
871 oe_debug_pub.add('sync_header_line - Inserting into oe_header_acks for p_hold_source_id is DONE');
872 END IF;
873 END IF;
874 END IF;
875
876 -- Start: commented out superfluous code for bug 8667900
877
878 /*
879 IF p_change_type = 'RELEASE' AND
880 p_order_hold_id IS NOT NULL THEN
881
882 FOR ord_rec IN get_ord_hld_cur
883 LOOP
884 IF l_debug_level > 0 THEN
885 oe_debug_pub.add('sync_header_line -RELEASE-QUERING HEADER RECORD');
886 END IF;
887
888 -- Bug 8463870
889 if ( p_header_rec.header_id IS NULL ) then
890 oe_debug_pub.add('.... Querying header_rec using API...');
891 OE_Header_UTIL.Query_Row
892 (p_header_id => ord_rec.header_id
893 ,x_header_rec => l_header_rec
894 );
895 else
896 oe_debug_pub.add('.... Assigning from passed in parameters...');
897 l_header_rec := p_header_rec;
898 end if;
899
900 IF ord_rec.line_id IS NOT NULL THEN
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add('sync_header_line -RELEASE-QUERING LINE RECORD');
903 END IF;
904
905 -- Bug 8463870
906 if ( p_line_rec.line_id is NULL ) then
907 oe_debug_pub.add('.... Querying line_rec using API...');
908 oe_line_util.query_row(
909 p_line_id => ord_rec.line_id
910 ,x_line_rec => l_line_rec
911 );
912 else
913 oe_debug_pub.add('... Assigning from passed in parameters...');
914 l_line_rec := p_line_rec;
915 end if;
916
917 select OE_XML_MESSAGE_SEQ_S.nextval
918 into l_itemkey
919 from dual;
920
921 IF l_debug_level > 0 THEN
922 oe_debug_pub.add('sync_header_line -RELEASE-BEFORE INSERTING HEADER ');
923 END IF;
924
925 INSERT_SYNC_HEADER(p_header_rec => l_header_rec,
926 p_change_type => null,
927 p_req_id => l_itemkey,
928 x_return_status => l_return_status);
929
930 IF l_debug_level > 0 THEN
931 oe_debug_pub.add('sync_header_line -RELEASE-BEFORE INSERTING LINE ');
932 END IF;
933 INSERT_SYNC_LINE(P_LINE_REC => l_line_rec,
934 p_change_type => p_change_type,
935 p_req_id => l_itemkey,
936 X_RETURN_STATUS => L_RETURN_STATUS);
937
938 IF l_debug_level > 0 THEN
939 oe_debug_pub.add('sync_header_line - Raise BPEL event by calling raise_bpel_out_event5');
940 END IF;
941
942 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
943 raise_bpel_out_event(
944 p_header_id => ord_rec.header_id
945 ,p_line_id => ord_rec.line_id
946 ,p_hdr_req_id => l_itemkey
947 ,p_lin_req_id => l_itemkey
948 ,p_change_type => p_change_type
949 ,p_hold_source_id => p_hold_source_id
950 ,p_order_hold_id => p_order_hold_id);
951 END IF;
952 ELSIF ord_rec.line_id IS NULL AND
953 ord_rec.header_id IS NOT NULL THEN
954
955 select OE_XML_MESSAGE_SEQ_S.nextval
956 into l_itemkey
957 from dual;
958
959 INSERT_SYNC_HEADER(p_header_rec => l_header_rec,
960 p_change_type => p_change_type,
961 p_req_id => l_itemkey, --XXXX
962 x_return_status => l_return_status);
963 IF l_debug_level > 0 THEN
964 oe_debug_pub.add('sync_header_line - Raise BPEL event by calling raise_bpel_out_event6');
965 END IF;
966
967 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
968 raise_bpel_out_event(p_header_id => ord_rec.header_id
969 ,p_line_id => ord_rec.line_id
970 ,p_hdr_req_id => l_itemkey --XXXX
971 ,p_lin_req_id => l_itemkey --XXXX
972 ,p_change_type => p_change_type
973 ,p_hold_source_id => p_hold_source_id
974 ,p_order_hold_id => p_order_hold_id);
975 END IF;
976 END IF;
977 END LOOP;
978 END IF;
979 */
980 -- Complete: commenting superfluous code bug 8667900
981
982 /*
983 IF l_debug_level > 0 THEN
984 oe_debug_pub.add('sync_header_line - Raise BPEL event by calling raise_bpel_out_event');
985 END IF;
986 */
987
988
989 ----
990 -- Here we prepare to process other change types (e.g., flow status change).
991 ----
992 IF p_change_type NOT IN ('APPLY','RELEASE') THEN
993
994 IF l_debug_level > 0 THEN
995 oe_debug_pub.add('sync_header_line - inserted into header acks');
996 END IF;
997 IF p_header_rec.header_id IS NOT NULL THEN
998
999 INSERT_SYNC_HEADER(p_header_rec => p_header_rec,
1000 p_change_type => p_change_type,
1001 p_req_id => p_hdr_req_id,
1002 x_return_status => l_return_status);
1003 END IF;
1004 IF l_debug_level > 0 THEN
1005 oe_debug_pub.add('sync_header_line - inserted into line acks');
1006 END IF;
1007
1008
1009 IF l_debug_level > 0 THEN
1010 oe_debug_pub.add('sync_header_line - before calling bpel');
1011 oe_debug_pub.add('sync_header_line - flow status'||p_line_rec.flow_status_code);
1012 END IF;
1013
1014 /* logging business event */
1015
1016 raise_bpel_out_event(p_header_id => p_header_rec.header_id
1017 ,p_line_id => p_line_rec.line_id
1018 ,p_hdr_req_id => p_hdr_req_id
1019 ,p_lin_req_id => p_lin_req_id --XXXX
1020 ,p_change_type => p_change_type
1021 ,p_hold_source_id => p_hold_source_id
1022 ,p_order_hold_id => p_order_hold_id);
1023 END IF;
1024
1025 IF l_debug_level > 0 THEN
1026 oe_debug_pub.add('OE_sync_order_PVT - Exiting sync_header_line');
1027 END IF;
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1031 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'sync_header_line');
1032 END IF;
1033
1034 END sync_header_line;
1035
1036
1037 PROCEDURE process_order_sync(p_header_id IN NUMBER
1038 ,p_hdr_req_id IN NUMBER
1039 ,p_line_id IN NUMBER
1040 ,p_lin_req_id IN NUMBER
1041 ,p_hold_source_id IN NUMBER
1042 ,p_order_hold_id IN NUMBER
1043 ,p_change_type IN VARCHAR2
1044 ,p_hdr_ack_tbl OUT NOCOPY oe_acknowledgment_pub.header_ack_tbl_type
1045 ,p_line_ack_tbl OUT NOCOPY oe_acknowledgment_pub.line_ack_tbl_type
1046 ,x_return_status OUT NOCOPY VARCHAR2
1047 ,x_msg_count OUT NOCOPY NUMBER
1048 ,x_msg_data OUT NOCOPY VARCHAR2
1049 ) IS
1050
1051 i NUMBER := 0;
1052 j NUMBER := 0;
1053 l_hdr_ack_rec oe_acknowledgment_pub.header_ack_rec_type;
1054 l_line_ack_rec oe_acknowledgment_pub.line_ack_rec_type;
1055
1056 -- Cursor modified for Enh. 7244277, 7644412
1057 CURSOR get_hdr_req_cur IS
1058 SELECT
1059 ---
1060 -- Bug 10009062 Fix: Retrieve ORIG_SYS_DOCUMENT_REF from
1061 -- Order Headers rather than from Header Acknowledgments.
1062 ---
1063 ack.HEADER_ID, hdr.ORIG_SYS_DOCUMENT_REF, ack.ORDER_NUMBER,
1064 ack.ORDERED_DATE ,ack.ORG_ID ,ack.CHANGE_DATE,
1065 ack.CHANGE_SEQUENCE ,ack.SOLD_TO_ORG_ID ,ack.ORDER_SOURCE_ID,
1066 ack.REQUEST_ID ,ack.ACKNOWLEDGMENT_TYPE ,ack.FLOW_STATUS_CODE,
1067 ack.INVOICE_ADDRESS_ID, ack.PRICE_LIST_ID,
1068 ack.TRANSACTIONAL_CURR_CODE --Bug 9182921
1069 FROM oe_header_acks ack,
1070 oe_order_headers_all hdr -- Bug 10009062 Fix
1071 WHERE ack.request_id = p_hdr_req_id
1072 AND hdr.header_id = ack.header_id;
1073
1074 -- Cursor modified for Enh. 7244277, 7644412, 7644426
1075 CURSOR get_line_req_cur IS
1076 SELECT
1077 HEADER_ID, ORIG_SYS_DOCUMENT_REF, ORIG_SYS_LINE_REF,
1078 CHANGE_DATE ,CHANGE_SEQUENCE ,ORDER_NUMBER,
1079 SOLD_TO_ORG_ID ,CONFIGURATION_ID ,CONFIG_REV_NBR,
1080 CONFIG_HEADER_ID ,CONFIG_LINE_REF ,TOP_MODEL_LINE_ID,
1081 INVENTORY_ITEM_ID ,LINE_ID ,LINE_NUMBER,
1082 ORDER_SOURCE_ID ,ORDERED_QUANTITY ,ORG_ID,
1083 REQUEST_ID ,SCHEDULE_ARRIVAL_DATE ,SCHEDULE_SHIP_DATE,
1084 ACKNOWLEDGMENT_TYPE ,FLOW_STATUS_CODE ,SPLIT_FROM_LINE_REF,
1085 SPLIT_FROM_SHIPMENT_REF ,SPLIT_FROM_LINE_ID, TAX_VALUE,
1086 AGREEMENT_ID, PAYMENT_TERM_ID, PROMISE_DATE, SHIP_FROM_ORG_ID,
1087 SHIPPING_METHOD_CODE, SHIPMENT_PRIORITY_CODE, FREIGHT_TERMS_CODE,
1088 SHIP_TO_CUSTOMER_ID, SHIP_TO_CONTACT_ID, SHIP_TO_ORG_ID,
1089 INVOICE_TO_CUSTOMER_ID, INVOICE_TO_CONTACT_ID, INVOICE_TO_ORG_ID,
1090 UNIT_SELLING_PRICE, PRICE_LIST_ID, UNIT_LIST_PRICE,
1091 UNIT_LIST_PRICE_PER_PQTY, UNIT_PERCENT_BASE_PRICE,
1092 UNIT_SELLING_PRICE_PER_PQTY, PRICING_DATE, SHIP_TO_ADDRESS_ID,
1093 SHIP_FROM_ORG, SHIP_TO_ORG, INVOICE_TO_ORG,
1094 ITEM_TYPE_CODE, -- 9131629
1095 LINE_CATEGORY_CODE -- 9151484
1096 FROM oe_line_acks
1097 WHERE request_id = p_lin_req_id;
1098
1099 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1100
1101 l_hdr_rec_ctr NUMBER := 0;
1102 l_line_rec_ctr NUMBER := 0;
1103
1104 BEGIN
1105
1106 IF l_debug_level > 0 THEN
1107 oe_debug_pub.add('OE_sync_order_PVT - Entering process_order_sync');
1108 oe_debug_pub.add('process_order_sync - p_header_id : '||p_header_id);
1109 oe_debug_pub.add('process_order_sync - p_line_id : '||p_line_id);
1110 oe_debug_pub.add('process_order_sync - p_hdr_req_id : '||p_hdr_req_id);
1111 oe_debug_pub.add('process_order_sync - p_lin_req_id : '||p_lin_req_id);
1112 oe_debug_pub.add('process_order_sync - p_change_type : '||p_change_type);
1113 oe_debug_pub.add('process_order_sync - p_hold_source_id: '||p_hold_source_id);
1114 oe_debug_pub.add('process_order_sync - p_order_hold_id : '||p_order_hold_id);
1115 END IF;
1116
1117
1118 IF p_hdr_req_id IS NOT NULL AND
1119 p_hdr_req_id <> 0 THEN
1120 IF l_debug_level > 0 THEN
1121 oe_debug_pub.add('process_order_sync - Getting data to populate p_hdr_ack_tbl');
1122 END IF;
1123 l_hdr_rec_ctr := 0;
1124 FOR hdr_rec IN get_hdr_req_cur LOOP
1125 l_hdr_rec_ctr := l_hdr_rec_ctr + 1;
1126 p_hdr_ack_tbl(l_hdr_rec_ctr).HEADER_ID := hdr_rec.HEADER_ID;
1127 p_hdr_ack_tbl(l_hdr_rec_ctr).ORIG_SYS_DOCUMENT_REF := hdr_rec.ORIG_SYS_DOCUMENT_REF;
1128 p_hdr_ack_tbl(l_hdr_rec_ctr).ORDER_NUMBER := hdr_rec.ORDER_NUMBER;
1129 p_hdr_ack_tbl(l_hdr_rec_ctr).ORDERED_DATE := hdr_rec.ORDERED_DATE;
1130 p_hdr_ack_tbl(l_hdr_rec_ctr).ORG_ID := hdr_rec.ORG_ID;
1131 p_hdr_ack_tbl(l_hdr_rec_ctr).CHANGE_DATE := hdr_rec.CHANGE_DATE;
1132 p_hdr_ack_tbl(l_hdr_rec_ctr).CHANGE_SEQUENCE := hdr_rec.CHANGE_SEQUENCE;
1133 p_hdr_ack_tbl(l_hdr_rec_ctr).SOLD_TO_ORG_ID := hdr_rec.SOLD_TO_ORG_ID;
1134 p_hdr_ack_tbl(l_hdr_rec_ctr).ORDER_SOURCE_ID := hdr_rec.ORDER_SOURCE_ID;
1135 p_hdr_ack_tbl(l_hdr_rec_ctr).REQUEST_ID := hdr_rec.REQUEST_ID;
1136 p_hdr_ack_tbl(l_hdr_rec_ctr).ACKNOWLEDGMENT_TYPE := hdr_rec.ACKNOWLEDGMENT_TYPE;
1137 p_hdr_ack_tbl(l_hdr_rec_ctr).FLOW_STATUS_CODE := hdr_rec.FLOW_STATUS_CODE;
1138 p_hdr_ack_tbl(l_hdr_rec_ctr).INVOICE_ADDRESS_ID := hdr_rec.INVOICE_ADDRESS_ID;
1139 p_hdr_ack_tbl(l_hdr_rec_ctr).PRICE_LIST_ID := hdr_rec.PRICE_LIST_ID;
1140 -- Bug 9131629
1141 p_hdr_ack_tbl(l_hdr_rec_ctr).TRANSACTIONAL_CURR_CODE := hdr_rec.TRANSACTIONAL_CURR_CODE;
1142 END LOOP;
1143 END IF;
1144
1145 IF p_lin_req_id IS NOT NULL AND
1146 p_lin_req_id <> 0 THEN
1147
1148 IF l_debug_level > 0 THEN
1149 oe_debug_pub.add('process_order_sync - Getting data to populate p_line_ack_tbl');
1150 END IF;
1151 l_line_rec_ctr := 0;
1152 FOR line_rec IN get_line_req_cur LOOP
1153 l_line_rec_ctr := l_line_rec_ctr + 1;
1154 p_line_ack_tbl(l_line_rec_ctr).HEADER_ID := line_rec.HEADER_ID;
1155 p_line_ack_tbl(l_line_rec_ctr).ORIG_SYS_DOCUMENT_REF := line_rec.ORIG_SYS_DOCUMENT_REF;
1156 p_line_ack_tbl(l_line_rec_ctr).ORIG_SYS_LINE_REF := line_rec.ORIG_SYS_LINE_REF;
1157 p_line_ack_tbl(l_line_rec_ctr).CHANGE_DATE := line_rec.CHANGE_DATE;
1158 p_line_ack_tbl(l_line_rec_ctr).CHANGE_SEQUENCE := line_rec.CHANGE_SEQUENCE;
1159 p_line_ack_tbl(l_line_rec_ctr).ORDER_NUMBER := line_rec.ORDER_NUMBER;
1160 p_line_ack_tbl(l_line_rec_ctr).SOLD_TO_ORG_ID := line_rec.SOLD_TO_ORG_ID;
1161 p_line_ack_tbl(l_line_rec_ctr).CONFIGURATION_ID := line_rec.CONFIGURATION_ID;
1162 p_line_ack_tbl(l_line_rec_ctr).CONFIG_REV_NBR := line_rec.CONFIG_REV_NBR;
1163 p_line_ack_tbl(l_line_rec_ctr).CONFIG_HEADER_ID := line_rec.CONFIG_HEADER_ID;
1164 p_line_ack_tbl(l_line_rec_ctr).CONFIG_LINE_REF := line_rec.CONFIG_LINE_REF;
1165 p_line_ack_tbl(l_line_rec_ctr).TOP_MODEL_LINE_ID := line_rec.TOP_MODEL_LINE_ID;
1166 p_line_ack_tbl(l_line_rec_ctr).INVENTORY_ITEM_ID := line_rec.INVENTORY_ITEM_ID;
1167 p_line_ack_tbl(l_line_rec_ctr).LINE_ID := line_rec.LINE_ID;
1168 p_line_ack_tbl(l_line_rec_ctr).LINE_NUMBER := line_rec.LINE_NUMBER;
1169 p_line_ack_tbl(l_line_rec_ctr).ORDER_SOURCE_ID := line_rec.ORDER_SOURCE_ID;
1170 p_line_ack_tbl(l_line_rec_ctr).ORDERED_QUANTITY := line_rec.ORDERED_QUANTITY;
1171 p_line_ack_tbl(l_line_rec_ctr).ORG_ID := line_rec.ORG_ID;
1172 p_line_ack_tbl(l_line_rec_ctr).REQUEST_ID := line_rec.REQUEST_ID;
1173 p_line_ack_tbl(l_line_rec_ctr).SCHEDULE_ARRIVAL_DATE := line_rec.SCHEDULE_ARRIVAL_DATE;
1174 p_line_ack_tbl(l_line_rec_ctr).SCHEDULE_SHIP_DATE := line_rec.SCHEDULE_SHIP_DATE;
1175 p_line_ack_tbl(l_line_rec_ctr).ACKNOWLEDGMENT_TYPE := line_rec.ACKNOWLEDGMENT_TYPE;
1176 p_line_ack_tbl(l_line_rec_ctr).FLOW_STATUS_CODE := line_rec.FLOW_STATUS_CODE;
1177 p_line_ack_tbl(l_line_rec_ctr).SPLIT_FROM_LINE_REF := line_rec.SPLIT_FROM_LINE_REF;
1178 p_line_ack_tbl(l_line_rec_ctr).SPLIT_FROM_SHIPMENT_REF:= line_rec.SPLIT_FROM_SHIPMENT_REF;
1179 p_line_ack_tbl(l_line_rec_ctr).SPLIT_FROM_LINE_ID := line_rec.SPLIT_FROM_LINE_ID;
1180 -- Start : Added for Enh. 7244277, 7644412, 7644426
1181 p_line_ack_tbl(l_line_rec_ctr).TAX_VALUE := line_rec.TAX_VALUE;
1182 p_line_ack_tbl(l_line_rec_ctr).AGREEMENT_ID := line_rec.AGREEMENT_ID;
1183 p_line_ack_tbl(l_line_rec_ctr).PAYMENT_TERM_ID := line_rec.PAYMENT_TERM_ID;
1184 p_line_ack_tbl(l_line_rec_ctr).PROMISE_DATE := line_rec.PROMISE_DATE;
1185 p_line_ack_tbl(l_line_rec_ctr).SHIP_FROM_ORG_ID := line_rec.SHIP_FROM_ORG_ID;
1186 p_line_ack_tbl(l_line_rec_ctr).SHIPPING_METHOD_CODE := line_rec.SHIPPING_METHOD_CODE;
1187 p_line_ack_tbl(l_line_rec_ctr).SHIPMENT_PRIORITY_CODE := line_rec.SHIPMENT_PRIORITY_CODE;
1188 p_line_ack_tbl(l_line_rec_ctr).FREIGHT_TERMS_CODE := line_rec.FREIGHT_TERMS_CODE;
1189 p_line_ack_tbl(l_line_rec_ctr).SHIP_TO_CUSTOMER_ID := line_rec.SHIP_TO_CUSTOMER_ID;
1190 p_line_ack_tbl(l_line_rec_ctr).SHIP_TO_CONTACT_ID := line_rec.SHIP_TO_CONTACT_ID;
1191 p_line_ack_tbl(l_line_rec_ctr).SHIP_TO_ORG_ID := line_rec.SHIP_TO_ORG_ID;
1192 p_line_ack_tbl(l_line_rec_ctr).INVOICE_TO_CUSTOMER_ID := line_rec.INVOICE_TO_CUSTOMER_ID;
1193 p_line_ack_tbl(l_line_rec_ctr).INVOICE_TO_CONTACT_ID := line_rec.INVOICE_TO_CONTACT_ID;
1194 p_line_ack_tbl(l_line_rec_ctr).INVOICE_TO_ORG_ID := line_rec.INVOICE_TO_ORG_ID;
1195 p_line_ack_tbl(l_line_rec_ctr).UNIT_SELLING_PRICE := line_rec.UNIT_SELLING_PRICE;
1196 p_line_ack_tbl(l_line_rec_ctr).PRICE_LIST_ID := line_rec.PRICE_LIST_ID;
1197 p_line_ack_tbl(l_line_rec_ctr).UNIT_LIST_PRICE := line_rec.UNIT_LIST_PRICE;
1198 p_line_ack_tbl(l_line_rec_ctr).UNIT_LIST_PRICE_PER_PQTY := line_rec.UNIT_LIST_PRICE_PER_PQTY;
1199 p_line_ack_tbl(l_line_rec_ctr).UNIT_PERCENT_BASE_PRICE := line_rec.UNIT_PERCENT_BASE_PRICE;
1200 p_line_ack_tbl(l_line_rec_ctr).UNIT_SELLING_PRICE_PER_PQTY := line_rec.UNIT_SELLING_PRICE_PER_PQTY;
1201 p_line_ack_tbl(l_line_rec_ctr).PRICING_DATE := line_rec.PRICING_DATE;
1202 p_line_ack_tbl(l_line_rec_ctr).SHIP_TO_ADDRESS_ID := line_rec.SHIP_TO_ADDRESS_ID;
1203 -- End : Added for Enh. 7244277, 7644412, 7644426
1204 p_line_ack_tbl(l_line_rec_ctr).SHIP_FROM_ORG := line_rec.SHIP_FROM_ORG;
1205 p_line_ack_tbl(l_line_rec_ctr).SHIP_TO_ORG := line_rec.SHIP_TO_ORG;
1206 p_line_ack_tbl(l_line_rec_ctr).INVOICE_TO_ORG := line_rec.INVOICE_TO_ORG;
1207 -- Bug 9131629
1208 p_line_ack_tbl(l_line_rec_ctr).ITEM_TYPE_CODE := line_rec.ITEM_TYPE_CODE;
1209 -- Bug 9151484
1210 p_line_ack_tbl(l_line_rec_ctr).LINE_CATEGORY_CODE := line_rec.LINE_CATEGORY_CODE;
1211 END LOOP;
1212 END IF;
1213
1214
1215 IF l_debug_level > 0 THEN
1216 oe_debug_pub.add('OE_SYNC_ORDER_PVT - Exiting process_order_sync');
1217 END IF;
1218
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 oe_debug_pub.add('EXCEPTION in OE_SYNC_ORDER_PVT');
1222 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1223 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'process_order_sync');
1224 END IF;
1225
1226 END process_order_sync;
1227
1228 PROCEDURE raise_bpel_out_event(p_header_id IN NUMBER DEFAULT NULL
1229 ,p_line_id IN NUMBER DEFAULT NULL
1230 ,p_hdr_req_id IN NUMBER DEFAULT NULL
1231 ,p_lin_req_id IN NUMBER DEFAULT NULL
1232 ,p_change_type IN VARCHAR2 DEFAULT NULL
1233 ,p_hold_source_id IN NUMBER DEFAULT NULL
1234 ,p_order_hold_id IN NUMBER DEFAULT NULL) IS
1235
1236 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1237 l_itemkey NUMBER;
1238 l_event_name VARCHAR2(50);
1239 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1240
1241 BEGIN
1242
1243 IF l_debug_level > 0 THEN
1244 oe_debug_pub.add('OE_SYNC_ORDER_PVT - Entering raise_bpel_out_event');
1245 oe_debug_pub.add('raise_bpel_out_event - p_header_id : '||p_header_id);
1246 oe_debug_pub.add('raise_bpel_out_event - p_line_id : '||p_line_id);
1247 oe_debug_pub.add('raise_bpel_out_event - p_hdr_req_id : '||p_hdr_req_id);
1248 oe_debug_pub.add('raise_bpel_out_event - p_lin_req_id : '||p_lin_req_id);
1249 oe_debug_pub.add('raise_bpel_out_event - p_change_type : '||p_change_type);
1250 oe_debug_pub.add('raise_bpel_out_event - p_hold_source_id: '||p_hold_source_id);
1251 oe_debug_pub.add('raise_bpel_out_event - p_order_hold_id : '||p_order_hold_id);
1252 oe_debug_pub.add('raise_bpel_out_event - Adding parameters to l_parameter_list');
1253 END IF;
1254
1255 wf_event.AddParameterToList(p_name => 'HEADER_ID'
1256 ,p_value => NVL(p_header_id, 0)
1257 ,p_parameterlist => l_parameter_list);
1258
1259 wf_event.AddParameterToList(p_name => 'LINE_ID'
1260 ,p_value => NVL(p_line_id, 0)
1261 ,p_parameterlist => l_parameter_list);
1262
1263 wf_event.AddParameterToList(p_name => 'HDR_REQ_ID'
1264 ,p_value => NVL(p_hdr_req_id, 0)
1265 ,p_parameterlist => l_parameter_list);
1266
1267 wf_event.AddParameterToList(p_name => 'LIN_REQ_ID'
1268 ,p_value => NVL(p_lin_req_id, 0)
1269 ,p_parameterlist => l_parameter_list);
1270
1271 wf_event.AddParameterToList(p_name => 'CHANGE_TYPE'
1272 ,p_value => NVL(p_change_type, 'XXX')
1273 ,p_parameterlist => l_parameter_list);
1274
1275 wf_event.AddParameterToList(p_name => 'HOLD_SOURCE_ID'
1276 ,p_value => NVL(p_hold_source_id, 0)
1277 ,p_parameterlist => l_parameter_list);
1278
1279 wf_event.AddParameterToList(p_name => 'ORDER_HOLD_ID'
1280 ,p_value => NVL(p_order_hold_id, 0)
1281 ,p_parameterlist => l_parameter_list);
1282
1283 IF l_debug_level > 0 THEN
1284 oe_debug_pub.add('raise_bpel_out_event - Finished adding parameters to l_parameter_list');
1285 END IF;
1286
1287 SELECT OE_XML_MESSAGE_SEQ_S.nextval /* New one to be seeded */
1288 INTO l_itemkey
1289 FROM DUAL;
1290
1291 IF l_debug_level > 0 THEN
1292 oe_debug_pub.add('raise_bpel_out_event - Generated value of l_itemkey: '||l_itemkey);
1293 END IF;
1294
1295 l_event_name := 'oracle.apps.ont.genesis.outbound.update';
1296 wf_event.raise(p_event_name => l_event_name
1297 ,p_event_key => l_itemkey
1298 ,p_parameters => l_parameter_list);
1299
1300 IF l_debug_level > 0 THEN
1301 oe_debug_pub.add('raise_bpel_out_event - l_event_name: '||l_event_name);
1302 oe_debug_pub.add('raise_bpel_out_event - Raising event...');
1303 END IF;
1304
1305
1306 IF l_debug_level > 0 THEN
1307 oe_debug_pub.add('OE_SYNC_ORDER_PVT - Exiting raise_bpel_out_event');
1308 END IF;
1309
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1313 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'raise_bpel_out_event');
1314 END IF;
1315
1316 END raise_bpel_out_event;
1317
1318 END OE_SYNC_ORDER_PVT;