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