1 PACKAGE BODY OE_Acknowledgment_Pub AS
2 /* $Header: OEXPACKB.pls 120.23.12020000.2 2012/07/03 10:00:14 amallik ship $ */
3
4 /* -----------------------------------------------------------------
5 -- API name OE_Acknowledgment_Pub
6 --
7 -- Type Public
8 -- Function
9 --
10 -- Pre-reqs
11 --
12 -- Parameters
13 --
14 -- Version Current version = 1.0
15 -- Initial version = 1.0
16 --
17 -- Notes
18 --
19 -- End of Comments
20 -- ----------------------------------------------------------------
21 */
22
23 -- Global constant holding the package name
24
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Acknowledgment_Pub';
26
27 -- { Start of the data type declaration for the Bulk Collect
28 -- DATA TYPES (RECORD/TABLE TYPES)
29
30 TYPE number_arr IS TABLE OF number;
31 TYPE char50_arr IS TABLE OF varchar2(50);
32
33 TYPE Order_Rec_Type IS RECORD
34 ( header_id number_arr := number_arr()
35 , sold_to_org_id number_arr := number_arr()
36 , order_number number_arr := number_arr()
37 , orig_sys_document_ref char50_arr := char50_arr()
38 , order_source_id number_arr := number_arr()
39 , change_sequence char50_arr := char50_arr()
40 , org_id number_arr := number_arr()
41 );
42
43 Procedure is_line_exists
44 (p_line_id IN NUMBER,
45 x_exists_flag OUT NOCOPY VARCHAR2); -- Bug 9685021
46
47
48 -- End of the data type declaration for the Bulk Collect}
49
50 Function Get_Orig_Sys_Document_Ref
51 (p_header_id In Number := Null,
52 p_line_id In Number := Null
53 ) Return Varchar2
54 Is
55 l_orig_sys_document_ref Varchar2(50) := Null;
56 --
57 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
58 --
59 Begin
60
61 If p_header_id Is Not Null Then
62 Select orig_sys_document_ref
63 Into l_orig_sys_document_ref
64 From oe_order_headers
65 Where header_id = p_header_id;
66 Else
67 Select orig_sys_document_ref
68 Into l_orig_sys_document_ref
69 From oe_order_lines
70 Where line_id = p_line_id;
71 End If;
72
73 Return l_orig_sys_document_ref;
74
75 Exception
76 When Others Then
77 IF l_debug_level > 0 THEN
78 oe_debug_pub.add( 'L_ORIG_SYS_DOCUMENT_REF IS NOT DERIVED , OTHERS EXCEPTION' ) ;
79 END IF;
80 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
81 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_orig_sys_document_ref');
82 End if;
83 Return l_orig_sys_document_ref;
84
85 End Get_Orig_Sys_Document_Ref;
86
87
88 Function Get_Header_Id
89 (p_orig_sys_document_ref In Varchar2 := Null,
90 p_line_id In Number := Null,
91 p_sold_to_org_id In Number
92 ) Return Number
93 Is
94 l_header_id Number := 0;
95 l_customer_key_profile VARCHAR2(1) := 'N';
96 --
97 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
98 --
99 Begin
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'ENTERING OEXPACKB GET_HEADER_ID' ) ;
102 END IF;
103
104 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
105 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
106 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
109 END IF;
110 End If;
111
112 If p_orig_sys_document_ref Is Not Null Then
113 IF l_debug_level > 0 THEN
114 oe_debug_pub.add( 'P_ORIG_SYS_DOCUMENT_REF IS NOT NULL'||P_ORIG_SYS_DOCUMENT_REF ) ;
115 END IF;
116 Select header_id
117 Into l_header_id
118 From oe_order_headers
119 Where order_source_id = G_XML_ORDER_SOURCE_ID
120 And orig_sys_document_ref = p_orig_sys_document_ref
121 AND decode(l_customer_key_profile, 'Y',
122 nvl(sold_to_org_id, -999), 1)
123 = decode(l_customer_key_profile, 'Y',
124 nvl(p_sold_to_org_id, -999), 1)
125 And rownum = 1;
126 IF l_debug_level > 0 THEN
127 oe_debug_pub.add( 'HEADER_ID FOR THE ORIG_SYS_DOCUMENT => ' || L_HEADER_ID ) ;
128 END IF;
129 Elsif p_line_id Is Not Null Then
130 IF l_debug_level > 0 THEN
131 oe_debug_pub.add( 'P_LINE_ID IS NOT NULL' ) ;
132 END IF;
133 Select header_id
134 Into l_header_id
135 From oe_order_lines
136 Where line_id = p_line_id
137 And order_source_id = G_XML_ORDER_SOURCE_ID
138 And rownum = 1;
139 IF l_debug_level > 0 THEN
140 oe_debug_pub.add( 'HEADER_ID FOR THE LINE_ID => ' || L_HEADER_ID ) ;
141 END IF;
142 End If;
143
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'EXITING OEXPACKB GET_HEADER_ID' ) ;
146 END IF;
147 return l_header_id;
148
149 Exception
150 When Others Then
151 IF l_debug_level > 0 THEN
152 oe_debug_pub.add( 'L_HEADER_ID IS NOT DERIVED , OTHERS EXCEPTION' ) ;
153 END IF;
154 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
155 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_header_id');
156 End if;
157 Return l_header_id;
158 End Get_Header_Id;
159
160
161 --{Start of Procedure to raise 3a4/3a9 event from Order Import
162 Procedure Raise_Event_From_Oeoi
163 ( p_transaction_type In Varchar2,
164 p_orig_sys_document_ref In Varchar2,
165 p_request_id In Number,
166 p_order_imported In Varchar2,
167 p_sold_to_org_id In Number,
168 p_change_sequence In Varchar2,
169 p_org_id In Number, --arihan
170 p_xml_message_id In Number,
171 p_start_from_flow In Varchar2,
172 p_check_for_delivery In Varchar2,
173 x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
174 )
175 Is
176 --Pragma AUTONOMOUS_TRANSACTION;
177 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
178 l_itemtype Varchar2(6);
179 l_itemkey Number;
180 l_event_name Varchar2(50);
181 l_start_from_flow Varchar2(4) := p_start_from_flow;
182 l_send_date Date := SYSDATE + .0005;
183 l_party_id NUMBER;
184 l_party_site_id NUMBER;
185 l_is_delivery_reqd Varchar2(1);
186 l_return_status Varchar2(30);
187 l_user_key Varchar2(240);
188 --
189 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
190 --
191 Begin
192 IF l_debug_level > 0 THEN
193 oe_debug_pub.add( 'ENTERING RAISE_EVENT_FROM_OEOI' ) ;
194 END IF;
195
196 l_event_name := 'oracle.apps.ont.oi.po_ack.create';
197
198 If l_start_from_flow is null then
199 l_start_from_flow := OE_ORDER_IMPORT_WF.G_WFI_IMPORT_PGM;
200 End If;
201
202 IF l_debug_level > 0 THEN
203 oe_debug_pub.add( 'L_START_FROM_FLOW = '||L_START_FROM_FLOW ) ;
204 END IF;
205
206 IF OE_Code_Control.Code_Release_Level >= '110510' THEN
207 IF p_check_for_delivery = 'Y' THEN
208 OE_Acknowledgment_Pub.Is_Delivery_Required
209 (
210 p_customer_id => p_sold_to_org_id,
211 p_transaction_type => G_TRANSACTION_TYPE,
212 p_transaction_subtype => G_TRANSACTION_POA,
213 p_org_id => p_org_id,
214 x_party_id => l_party_id,
215 x_party_site_id => l_party_site_id,
216 x_is_delivery_required => l_is_delivery_reqd,
217 x_return_status => l_return_status
218 );
219 If nvl(l_is_delivery_reqd, 'N') = 'N' Then
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 return;
222 End If;
223 END IF;
224 END IF;
225 Select Oe_Xml_Message_Seq_S.nextval
226 Into l_itemkey
227 From dual;
228 IF l_debug_level > 0 THEN
229 oe_debug_pub.add( 'L_ITEMKEY = '||L_ITEMKEY || ' ORG ID passed in is ' || p_org_id ) ;
230 END IF;
231
232
233 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
234 l_user_key := p_orig_sys_document_ref || ',' || to_char(p_sold_to_org_id) || ',' || p_change_sequence || ',' || p_transaction_type;
235 Else
236 l_user_key := p_orig_sys_document_ref || ',' || to_char(p_sold_to_org_id) || ',' || p_transaction_type;
237 End If;
238
239
240 wf_event.AddParameterToList(p_name=> 'ORIG_SYS_DOCUMENT_REF',
241 p_value=> p_orig_sys_document_ref,
242 p_parameterlist=> l_parameter_list);
243 wf_event.AddParameterToList(p_name=> 'PARAMETER3',
244 p_value=> p_transaction_type,
245 p_parameterlist=> l_parameter_list);
246 wf_event.AddParameterToList(p_name=> 'PARAMETER4',
247 p_value=> p_sold_to_org_id,
248 p_parameterlist=> l_parameter_list);
249 wf_event.AddParameterToList(p_name=> 'PARAMETER7',
250 p_value=> p_change_sequence,
251 p_parameterlist=> l_parameter_list);
252 wf_event.AddParameterToList(p_name=> 'START_FROM_FLOW',
253 p_value=> l_start_from_flow,
254 p_parameterlist=> l_parameter_list);
255 wf_event.AddParameterToList(p_name=> 'REQ_ID',
256 p_value=> p_request_id,
257 p_parameterlist=> l_parameter_list);
258 wf_event.AddParameterToList(p_name=> 'ORDER_IMPORTED',
259 p_value=> p_order_imported,
260 p_parameterlist=> l_parameter_list);
261 wf_event.AddParameterToList(p_name=> 'USER_KEY',
262 p_value=> l_user_key,
263 p_parameterlist=> l_parameter_list);
264 wf_event.AddParameterToList(p_name=> 'ORG_ID',
265 p_value=> p_org_id,
266 p_parameterlist=> l_parameter_list);
267 wf_event.AddParameterToList(p_name=> 'PARAMETER5',
268 p_value=> p_xml_message_id,
269 p_parameterlist=> l_parameter_list);
270 wf_event.AddParameterToList(p_name=> 'ECX_PARTY_ID',
271 p_value=> l_party_id,
272 p_parameterlist=> l_parameter_list);
273 wf_event.AddParameterToList(p_name=> 'ECX_PARTY_SITE_ID',
274 p_value=> l_party_site_id,
275 p_parameterlist=> l_parameter_list);
276 IF l_debug_level > 0 THEN
277 oe_debug_pub.add( 'BEFORE RAISE EVENT ORACLE.APPS.ONT.OI.PO_ACK.CREATE' ) ;
278 END IF;
279 wf_event.raise( p_event_name => l_event_name,
280 p_event_key => l_itemkey,
281 p_parameters => l_parameter_list);
282 --p_send_date => l_send_date);
283
284 l_parameter_list.DELETE;
285
286 -- Up to your own code to commit the transaction
287 If l_itemtype <> OE_ORDER_IMPORT_WF.G_WFI_IMPORT_PGM then
288 IF l_debug_level > 0 THEN
289 oe_debug_pub.add( 'BEFORE COMMIT' ) ;
290 END IF;
291 Commit;
292 End if;
293
294 -- Remove this after finding the cause
295 -- Autonomous transaction
296 Commit;
297
298 -- Up to your code to handle any major exceptions
299 -- The Business Event System is unlikely to return any errors
300 -- As long as the Raise can be submitted, any errors will be placed
301 -- on the WF_ERROR queue and a notification sent to SYSADMIN
302
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 IF l_debug_level > 0 THEN
306 oe_debug_pub.add( 'EXITING RAISE_EVENT_FROM_OEOI' ) ;
307 END IF;
308 Exception
309 when others then
310 IF l_debug_level > 0 THEN
311 oe_debug_pub.add( 'ENCOUNTERED OTHERS EXCEPTION IN RAISE_EVENT_FROM_OEOI' ) ;
312 END IF;
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
315 THEN
316 FND_MSG_PUB.Add_Exc_Msg
317 (G_PKG_NAME, 'OE_Acknowledgment_Pub.Raise_Event_From_Oeoi');
318 END IF;
319
320
321 End Raise_Event_From_Oeoi;
322
323
324 --{Start of Procedure to raise 3a6 event
325 Procedure Raise_Event_Showso
326 ( p_header_id In Number,
327 p_line_id In Number,
328 p_customer_id In Number,
329 p_orig_sys_document_ref In Varchar2,
330 p_change_sequence In Varchar2,
331 p_itemtype In Varchar2,
332 p_itemkey In Number,
333 p_party_id In Number,
334 p_party_site_id In Number,
335 p_transaction_type In Varchar2,
336 p_request_id In Number,
337 p_commit_flag In Varchar2,
338 p_org_id In Number,
339 x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
340 )
341 Is
342 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
343 l_orig_sys_document_ref Varchar2(50);
344 l_itemtype Varchar2(6);
345 l_itemkey Number;
346 l_org_id Number;
347 l_transaction_type Varchar2(6) := p_transaction_type;
348 --
349 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
350 --
351 Begin
352
353
354 -- We know that we are going to raise an event, so
355 -- we will set some parameters now, these will become
356 -- item attributes in any workflow processes that called
357 -- by the business event system due to this business event
358 -- Up to 100 name/value pairs. The fewer the parameters the
359 -- better performance will be.
360
361 IF l_debug_level > 0 THEN
362 oe_debug_pub.add( 'ENTERING RAISE_EVENT_SHOWSO' ) ;
363 END IF;
364
365 if p_itemkey is null then
366
367 IF l_debug_level > 0 THEN
368 oe_debug_pub.add( 'passed-in itemkey was null...pulling value from sequence') ;
369 end if;
370
371 select OE_XML_MESSAGE_SEQ_S.nextval
372 into l_itemkey
373 from dual;
374 else
375
376 l_itemkey := p_itemkey;
377
378 end if;
379
380 IF l_debug_level > 0 THEN
381 oe_debug_pub.add( 'ITEMKEY (request_id) => ' || l_itemkey) ;
382 end if;
383
384 If p_orig_sys_document_ref Is Null Then
385 l_orig_sys_document_ref := Get_Orig_Sys_document_Ref
386 (p_header_id => p_header_id,
387 p_line_id => p_line_id);
388 Else
389 l_orig_sys_document_ref := p_orig_sys_document_ref;
390 End If;
391
392 IF l_debug_level > 0 THEN
393 oe_debug_pub.add( 'AFTER GET_ORIG_SYS_DOCUMENT_REF => ' || L_ORIG_SYS_DOCUMENT_REF ) ;
394 END IF;
395
396 wf_event.AddParameterToList(p_name=> 'ORG_ID',
397 p_value=> p_org_id,
398 p_parameterlist=> l_parameter_list);
399
400 IF l_debug_level > 0 THEN
401 oe_debug_pub.add( 'AFTER ADDING ORG_ID PARAMETER..ORG_ID IS => ' || L_ORG_ID ) ;
402 END IF;
403
404 wf_event.AddParameterToList(p_name=> 'HEADER_ID',
405 p_value=> p_header_id,
406 p_parameterlist=> l_parameter_list);
407 IF l_debug_level > 0 THEN
408 oe_debug_pub.add( 'AFTER ADDING HEADER_ID PARAMETER..HEADER_ID IS => ' || P_HEADER_ID ) ;
409 END IF;
410 wf_event.AddParameterToList(p_name=> 'ORIG_SYS_DOCUMENT_REF',
411 p_value=> l_orig_sys_document_ref,
412 p_parameterlist=> l_parameter_list);
413
414 wf_event.AddParameterToList(p_name=> 'LINE_ID',
415 p_value=> p_line_id,
416 p_parameterlist=> l_parameter_list);
417 IF l_debug_level > 0 THEN
418 oe_debug_pub.add( 'AFTER ADDING LINE_ID PARAMETER..LINE_ID IS => ' || P_LINE_ID ) ;
419 END IF;
420 wf_event.AddParameterToList(p_name=> 'CUSTOMER_ID',
421 p_value=> p_customer_id,
422 p_parameterlist=> l_parameter_list);
423
424 wf_event.AddParameterToList(p_name=> 'PARAMETER4',
425 p_value=> p_customer_id,
426 p_parameterlist=> l_parameter_list);
427
428 wf_event.AddParameterToList(p_name=> 'PARAMETER7',
429 p_value=> p_change_sequence,
430 p_parameterlist=> l_parameter_list);
431
432
433 wf_event.AddParameterToList(p_name=> 'ECX_PARTY_ID',
434 p_value=> p_party_id,
435 p_parameterlist=> l_parameter_list);
436
437 wf_event.AddParameterToList(p_name=> 'ECX_PARTY_SITE_ID',
438 p_value=> p_party_site_id,
439 p_parameterlist=> l_parameter_list);
440
441 wf_event.AddParameterToList(p_name=> 'ECX_DOCUMENT_ID',
442 p_value=> p_itemkey,
443 p_parameterlist=> l_parameter_list);
444
445 If l_transaction_type Is NULL Then
446 l_transaction_type := G_TRANSACTION_SSO; -- we default to SSO, but it can also be passed in as CSO
447 End If;
448
449 wf_event.AddParameterToList(p_name=> 'PARAMETER3',
450 p_value=> l_transaction_type,
451 p_parameterlist=> l_parameter_list);
452
453 IF l_debug_level > 0 THEN
454 oe_debug_pub.add( 'AFTER ADD PARAMETERS' ) ;
455 END IF;
456
457 If p_itemtype Is Null Then
458 l_itemtype := OE_ORDER_IMPORT_WF.G_WFI_PROC;
459 Else
460 l_itemtype := p_itemtype;
461 End If;
462 wf_event.AddParameterToList(p_name=> 'START_FROM_FLOW',
463 p_value=> l_itemtype,
464 p_parameterlist=> l_parameter_list);
465
466 IF l_debug_level > 0 THEN
467 oe_debug_pub.add( 'start_from_flow:' || l_itemtype) ;
468 END IF;
469
470 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
471 IF l_itemtype = OE_ORDER_IMPORT_WF.G_WFI_CONC_PGM THEN
472 wf_event.AddParameterToList(p_name=> 'REQ_ID',
473 p_value=> p_request_id,
474 p_parameterlist=> l_parameter_list);
475 END IF;
476 END IF;
477
478 /* Not needed now with unique itemkey, remove after testing
479 -- Purge any existing workflow with the same itemkey/itemtype
480 oe_order_import_wf.call_wf_purge(p_itemtype => 'OESO',
481 p_itemkey => l_itemkey);
482 */
483 -- Raise the event with no XML document, if an XML
484 -- document is required to be generated, the Generate Function
485 -- will automatically be run
486
487 IF l_debug_level > 0 THEN
488 oe_debug_pub.add( 'BEFORE RAISE EVENT ORACLE.APPS.ONT.OI.SHOW_SO.CREATE' ) ;
489 END IF;
490 wf_event.raise( p_event_name => 'oracle.apps.ont.oi.show_so.create',
491 p_event_key => l_itemkey,
492 p_parameters => l_parameter_list);
493
494 l_parameter_list.DELETE;
495
496 -- Up to your own code to commit the transaction
497 If l_itemtype <> OE_ORDER_IMPORT_WF.G_WFI_PROC AND
498 p_commit_flag = 'Y' then
499 IF l_debug_level > 0 THEN
500 oe_debug_pub.add( 'BEFORE COMMIT' ) ;
501 END IF;
502 Commit;
503 End if;
504
505 -- Up to your code to handle any major exceptions
506 -- The Business Event System is unlikely to return any errors
507 -- As long as the Raise can be submitted, any errors will be placed
508 -- on the WF_ERROR queue and a notification sent to SYSADMIN
509
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511
512 IF l_debug_level > 0 THEN
513 oe_debug_pub.add( 'EXITING RAISE_EVENT_SHOWSO' ) ;
514 END IF;
515 Exception
516 when others then
517 IF l_debug_level > 0 THEN
518 oe_debug_pub.add( 'ENCOUNTERED OTHERS EXCEPTION IN RAISE_EVENT_SHOWSO: ' || sqlerrm) ;
519 END IF;
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
522 THEN
523 FND_MSG_PUB.Add_Exc_Msg
524 (G_PKG_NAME, 'OE_Acknowledgment_Pub.Raise_Event_Showso');
525 END IF;
526
527 End Raise_Event_Showso;
528 -- End of Procedure to raise 3a6 event}
529
530 PROCEDURE Process_Acknowledgment
531 (p_api_version_number IN NUMBER
532 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
533
534 ,p_header_rec IN OE_Order_Pub.Header_Rec_Type :=
535 OE_Order_Pub.G_MISS_HEADER_REC
536 ,p_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type :=
537 OE_Order_Pub.G_MISS_HEADER_VAL_REC
538 ,p_Header_Adj_tbl IN OE_Order_Pub.Header_Adj_Tbl_Type :=
539 OE_Order_Pub.G_MISS_HEADER_ADJ_TBL
540 ,p_Header_Adj_val_tbl IN OE_Order_Pub.Header_Adj_Val_Tbl_Type :=
541 OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL
542 ,p_Header_Scredit_tbl IN OE_Order_Pub.Header_Scredit_Tbl_Type :=
543 OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL
544 ,p_Header_Scredit_val_tbl IN OE_Order_Pub.Header_Scredit_Val_Tbl_Type :=
545 OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL
546 ,p_line_tbl IN OE_Order_Pub.Line_Tbl_Type :=
547 OE_Order_Pub.G_MISS_LINE_TBL
548 ,p_line_val_tbl IN OE_Order_Pub.Line_Val_Tbl_Type :=
549 OE_Order_Pub.G_MISS_LINE_VAL_TBL
550 ,p_Line_Adj_tbl IN OE_Order_Pub.Line_Adj_Tbl_Type :=
551 OE_Order_Pub.G_MISS_LINE_ADJ_TBL
552 ,p_Line_Adj_val_tbl IN OE_Order_Pub.Line_Adj_Val_Tbl_Type :=
553 OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL
554 ,p_Line_Scredit_tbl IN OE_Order_Pub.Line_Scredit_Tbl_Type :=
555 OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL
556 ,p_Line_Scredit_val_tbl IN OE_Order_Pub.Line_Scredit_Val_Tbl_Type :=
557 OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL
558 ,p_Lot_Serial_tbl IN OE_Order_Pub.Lot_Serial_Tbl_Type :=
559 OE_Order_Pub.G_MISS_LOT_SERIAL_TBL
560 ,p_Lot_Serial_val_tbl IN OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
561 OE_Order_Pub.G_MISS_LOT_SERIAL_VAL_TBL
562 ,p_action_request_tbl IN OE_Order_Pub.Request_Tbl_Type :=
563 OE_Order_Pub.G_MISS_REQUEST_TBL
564
565 ,p_old_header_rec IN OE_Order_Pub.Header_Rec_Type :=
566 OE_Order_Pub.G_MISS_HEADER_REC
567 ,p_old_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type :=
568 OE_Order_Pub.G_MISS_HEADER_VAL_REC
569 ,p_old_Header_Adj_tbl IN OE_Order_Pub.Header_Adj_Tbl_Type :=
570 OE_Order_Pub.G_MISS_HEADER_ADJ_TBL
571 ,p_old_Header_Adj_val_tbl IN OE_Order_Pub.Header_Adj_Val_Tbl_Type :=
572 OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL
573 ,p_old_Header_Scredit_tbl IN OE_Order_Pub.Header_Scredit_Tbl_Type :=
574 OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL
575 ,p_old_Header_Scredit_val_tbl IN OE_Order_Pub.Header_Scredit_Val_Tbl_Type :=
576 OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL
577 ,p_old_line_tbl IN OE_Order_Pub.Line_Tbl_Type :=
578 OE_Order_Pub.G_MISS_LINE_TBL
579 ,p_old_line_val_tbl IN OE_Order_Pub.Line_Val_Tbl_Type :=
580 OE_Order_Pub.G_MISS_LINE_VAL_TBL
581 ,p_old_Line_Adj_tbl IN OE_Order_Pub.Line_Adj_Tbl_Type :=
582 OE_Order_Pub.G_MISS_LINE_ADJ_TBL
583 ,p_old_Line_Adj_val_tbl IN OE_Order_Pub.Line_Adj_Val_Tbl_Type :=
584 OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL
585 ,p_old_Line_Scredit_tbl IN OE_Order_Pub.Line_Scredit_Tbl_Type :=
586 OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL
587 ,p_old_Line_Scredit_val_tbl IN OE_Order_Pub.Line_Scredit_Val_Tbl_Type :=
588 OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL
589 ,p_old_Lot_Serial_tbl IN OE_Order_Pub.Lot_Serial_Tbl_Type :=
590 OE_Order_Pub.G_MISS_LOT_SERIAL_TBL
591 ,p_old_Lot_Serial_val_tbl IN OE_Order_Pub.Lot_Serial_Val_Tbl_Type :=
592 OE_Order_Pub.G_MISS_LOT_SERIAL_VAL_TBL
593
594 ,p_buyer_seller_flag IN VARCHAR2
595 ,p_reject_order IN VARCHAR2
596
597 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
598 )
599 IS
600 l_api_version_number CONSTANT NUMBER := 1.0;
601 l_api_name CONSTANT VARCHAR2(30):= 'Process_Acknowledgment';
602 l_control_rec OE_GLOBALS.Control_Rec_Type;
603 l_return_status VARCHAR2(1);
604 l_msg_count NUMBER;
605 l_msg_data VARCHAR2(2000);
606 --
607 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
608 --
609 BEGIN
610
611 -- Standard call to check for call compatibility
612
613 IF l_debug_level > 0 THEN
614 oe_debug_pub.add( 'Entering Process Acknowledgment');
615 END IF;
616 IF NOT FND_API.Compatible_API_Call
617 ( l_api_version_number
618 , p_api_version_number
619 , l_api_name
620 , G_PKG_NAME
621 )
622 THEN
623 IF l_debug_level > 0 THEN
624 oe_debug_pub.add( 'OEXPACKB COMPATIBLE_API_CALL' ) ;
625 END IF;
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
627 END IF;
628
629 IF l_debug_level > 0 THEN
630 oe_debug_pub.add( 'BEFORE CALLING PRIVATE ACKNOWLEDGMENT API' ) ;
631 END IF;
632
633 OE_Acknowledgment_Pvt.Process_Acknowledgment
634 ( p_api_version_number => 1.0
635 , p_init_msg_list => p_init_msg_list
636
637 , p_header_rec => p_header_rec
638 , p_header_val_rec => p_header_val_rec
639 , p_header_adj_tbl => p_header_adj_tbl
640 , p_header_adj_val_tbl => p_header_adj_val_tbl
641 , p_header_scredit_tbl => p_header_scredit_tbl
642 , p_header_scredit_val_tbl => p_header_scredit_val_tbl
643 , p_line_tbl => p_line_tbl
644 , p_line_val_tbl => p_line_val_tbl
645 , p_line_adj_tbl => p_line_adj_tbl
646 , p_line_adj_val_tbl => p_line_adj_val_tbl
647 , p_line_scredit_tbl => p_line_scredit_tbl
648 , p_line_scredit_val_tbl => p_line_scredit_val_tbl
649 , p_lot_serial_tbl => p_lot_serial_tbl
650 , p_lot_serial_val_tbl => p_lot_serial_val_tbl
651 , p_action_request_tbl => p_action_request_tbl
652
653 , p_old_header_rec => p_old_header_rec
654 , p_old_header_val_rec => p_old_header_val_rec
655 , p_old_header_adj_tbl => p_old_header_adj_tbl
656 , p_old_header_adj_val_tbl => p_old_header_adj_val_tbl
657 , p_old_header_scredit_tbl => p_old_header_scredit_tbl
658 , p_old_header_scredit_val_tbl => p_old_header_scredit_val_tbl
659 , p_old_line_tbl => p_old_line_tbl
660 , p_old_line_val_tbl => p_old_line_val_tbl
661 , p_old_line_adj_tbl => p_old_line_adj_tbl
662 , p_old_line_adj_val_tbl => p_old_line_adj_val_tbl
663 , p_old_line_scredit_tbl => p_old_line_scredit_tbl
664 , p_old_line_scredit_val_tbl => p_old_line_scredit_val_tbl
665 , p_old_lot_serial_tbl => p_old_lot_serial_tbl
666 , p_old_lot_serial_val_tbl => p_old_lot_serial_val_tbl
667
668 , p_buyer_seller_flag => p_buyer_seller_flag
669 , p_reject_order => p_reject_order
670
671 , x_return_status => l_return_status
672 );
673
674 IF l_debug_level > 0 THEN
675 oe_debug_pub.add( 'AFTER CALLING PRIVATE ACKNOWLEDGMENT API' ) ;
676 END IF;
677
678 EXCEPTION
679
680 WHEN FND_API.G_EXC_ERROR THEN
681 IF l_debug_level > 0 THEN
682 oe_debug_pub.add( 'ENCOUNTERED ERROR EXCEPTION' ) ;
683 END IF;
684 x_return_status := FND_API.G_RET_STS_ERROR;
685
686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
687 IF l_debug_level > 0 THEN
688 oe_debug_pub.add( 'ENCOUNTERED UNEXPECTED ERROR EXCEPTION' ) ;
689 END IF;
690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
691
692 WHEN OTHERS THEN
693 IF l_debug_level > 0 THEN
694 oe_debug_pub.add( 'ENCOUNTERED OTHERS ERROR EXCEPTION IN OE_ACKNOWLEDGMENT_PUB.PROCESS_ACKNOWLEDGMENT: '|| SQLERRM ) ;
695 END IF;
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
697
698 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
699 THEN
700 FND_MSG_PUB.Add_Exc_Msg
701 (G_PKG_NAME, 'OE_Acknowledgment_Pub.Process_Acknowledgment');
702 END IF;
703
704
705 END Process_Acknowledgment;
706
707 -- aksingh 3A4 Start
708
709 -- {Start of function to get the index for the tbl for give line_id
710 FUNCTION Get_Line_Index
711 ( p_line_tbl IN OE_Order_Pub.Line_Tbl_Type,
712 p_line_id IN Number
713 )
714 RETURN NUMBER
715 IS
716 i pls_integer;
717 --
718 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
719 --
720 BEGIN
721 IF l_debug_level > 0 THEN
722 oe_debug_pub.add( 'ENTERNING: OEXPACK FUNCTION GET_LINE_INDEX' ) ;
723 END IF;
724 -- Following For loop is changed to while because of new Notify_OC change
725 -- for i in 1..p_line_tbl.count
726 -- loop
727 i := p_line_tbl.First;
728 while i is not null loop
729 if p_line_tbl(i).line_id = p_line_id
730 then
731 IF l_debug_level > 0 THEN
732 oe_debug_pub.add( 'EXITING: OEXPACK FUNCTION GET_LINE_INDEX - RETURNING ' || I ) ;
733 END IF;
734 return i;
735 end if;
736 i := p_line_tbl.Next(i);
737 end loop;
738 IF l_debug_level > 0 THEN
739 oe_debug_pub.add( 'EXITING: OEXPACK FUNCTION GET_LINE_INDEX - RETURNING 0' ) ;
740 END IF;
741 return 0;
742 EXCEPTION
743 WHEN OTHERS THEN
744 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
745 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'get_line_index');
746 END IF;
747 END Get_Line_Index;
748
749 -- {Start of procedure Insert_Header
750 PROCEDURE Insert_Header
751 ( p_header_rec IN OE_Order_Pub.Header_Rec_Type,
752 p_header_status IN Varchar2,
753 p_ack_type IN Varchar2,
754 p_itemkey IN Number,
755 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
756 )
757 IS
758
759 l_header_status varchar2(30);
760 --
761 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
762 --
763 BEGIN
764 IF l_debug_level > 0 THEN
765 oe_debug_pub.add( 'ENTERNING: OEXPACK PROCEDURE INSERT_HEADER' ) ;
766 END IF;
767
768 l_header_status := p_header_status;
769
770 If p_header_rec.flow_status_code = 'CANCELLED' then
771 l_header_status := 'CANCELLED';
772 Elsif p_header_rec.flow_status_code = 'CLOSED' then
773 l_header_status := 'CLOSED';
774 Else
775 l_header_status := 'OPEN';
776 End If;
777
778
779
780 Insert Into OE_HEADER_ACKS (header_id, acknowledgment_type, last_ack_code, request_id, sold_to_org_id, change_sequence)
781 Values (p_header_rec.header_id, p_ack_type, l_header_status, p_itemkey, --p_header_rec.request_id
782 p_header_rec.sold_to_org_id, p_header_rec.change_sequence);
783
784 if sql%rowcount > 0 then
785 IF l_debug_level > 0 THEN
786 oe_debug_pub.add( 'INSERTED HEADER_ID => ' || P_HEADER_REC.HEADER_ID ) ;
787 END IF;
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789 else
790 IF l_debug_level > 0 THEN
791 oe_debug_pub.add( 'NOT INSERTED HEADER_ID => ' || P_HEADER_REC.HEADER_ID ) ;
792 END IF;
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 end if;
795
796 IF l_debug_level > 0 THEN
797 oe_debug_pub.add( 'EXITING: OEXPACK PROCEDURE INSERT_HEADER' ) ;
798 END IF;
799 EXCEPTION
800 WHEN OTHERS THEN
801 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
802 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'insert_header');
803 END IF;
804 END Insert_Header;
805 -- End of procedure Insert_Header }
806
807 -- {Start of procedure Insert_Line
808 PROCEDURE Insert_Line
809 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type,
810 p_line_status IN Varchar2,
811 p_ack_type IN Varchar2,
812 p_itemkey IN Number,
813 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
814 )
815 IS
816 --
817 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
818 --
819 BEGIN
820 IF l_debug_level > 0 THEN
821 oe_debug_pub.add( 'ENTERNING: OEXPACK PROCEDURE INSERT_LINE' ) ;
822 END IF;
823
824 Insert Into OE_LINE_ACKS (header_id, line_id,
825 acknowledgment_type, last_ack_code, request_id,
826 sold_to_org_id, change_sequence)
827 Values (p_line_rec.header_id, p_line_rec.line_id,
828 p_ack_type, p_line_status,
829 p_itemkey, --p_line_rec.request_id
830 p_line_rec.sold_to_org_id,
831 p_line_rec.change_sequence
832 );
833 if sql%rowcount > 0 then
834 IF l_debug_level > 0 THEN
835 oe_debug_pub.add( 'INSERTED LINE_ID => ' || P_LINE_REC.LINE_ID ) ;
836 END IF;
837 x_return_status := FND_API.G_RET_STS_SUCCESS;
838 else
839 IF l_debug_level > 0 THEN
840 oe_debug_pub.add( 'NOT INSERTED LINE_ID => ' || P_LINE_REC.LINE_ID ) ;
841 END IF;
842 x_return_status := FND_API.G_RET_STS_ERROR;
843 end if;
844
845 IF l_debug_level > 0 THEN
846 oe_debug_pub.add( 'EXITING: OEXPACK PROCEDURE INSERT_LINE' ) ;
847 END IF;
848 EXCEPTION
849 WHEN OTHERS THEN
850 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
851 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'insert_line');
852 END IF;
853 END Insert_Line;
854 -- End of procedure Insert_Line }
855
856
857 -- {Start of procedure Query Interface Records
858 PROCEDURE Query_Inf_Records
859 ( p_order_source_id IN Number := 20,
860 p_orig_sys_document_ref IN Varchar2,
861 p_sold_to_org_id IN Number,
862 p_change_sequence IN Varchar2,
863 p_msg_id IN Number := NULL,
864 p_request_id IN Number := NuLL,
865 p_xml_transaction_type_code IN Varchar2,
866 x_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_Pub.Header_Rec_Type,
867 x_header_val_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_Pub.Header_Val_Rec_Type,
868 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_Pub.Line_Tbl_Type,
869 x_line_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_Pub.Line_Val_Tbl_Type)
870 IS
871 l_header_rec OE_Order_Pub.Header_Rec_Type;
872 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type;
873 l_line_rec OE_Order_Pub.Line_Rec_Type;
874 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
875 l_line_count Number := 0;
876 l_order_source_id Number;
877 l_orig_sys_document_ref Varchar2(50);
878 l_change_sequence Varchar2(50);
879 l_return_status Varchar2(1) := fnd_api.g_ret_sts_success;
880 l_customer_key_profile VARCHAR2(1) := 'N';
881
882
883 -- { Start of Header Interface Cursor
884 CURSOR l_header_cursor IS
885 SELECT order_source_id , orig_sys_document_ref
886 , change_sequence , booked_flag
887 , customer_number , customer_po_number
888 , freight_terms_code , freight_terms
889 , fob_point_code , fob_point
890 , invoice_to_org_id , invoice_to_org
891 , invoice_address1 , invoice_address2
892 , invoice_address3 , invoice_city
893 , invoice_state , invoice_postal_code
894 , invoice_county , invoice_country
895 , ship_from_org_id , ship_from_org
896 -- ?? Should we add all the ship from address columns??
897 , ship_to_org_id , ship_to_org
898 , ship_to_address1 , ship_to_address2
899 , ship_to_address3 , ship_to_city
900 , ship_to_state , ship_to_postal_code
901 , ship_to_county , ship_to_country
902 -- ?? Should we add all the sold to address columns??
903 , sold_to_org_id , sold_to_org
904 , org_id , request_id
905 , xml_message_id , payment_term
906 FROM oe_headers_interface
907 WHERE order_source_id = p_order_source_id
908 AND orig_sys_document_ref = p_orig_sys_document_ref
909 AND decode(l_customer_key_profile, 'Y',
910 nvl(sold_to_org_id, -999), 1)
911 = decode(l_customer_key_profile, 'Y',
912 nvl(p_sold_to_org_id, -999), 1)
913 AND nvl(change_sequence, ' ')
914 = nvl(p_change_sequence, ' ')
915 AND nvl(request_id, -999)
916 = nvl(p_request_id, -999)
917 AND xml_transaction_type_code = p_xml_transaction_type_code
918 AND error_flag = 'Y'
919 -- FOR UPDATE NOWAIT
920 ;
921 -- End of Header Interface Cursor}
922
923 -- { Start of Line Interface Cursor
924 CURSOR l_line_cursor IS
925 SELECT order_source_id , orig_sys_document_ref
926 , customer_item_name , customer_item_id
927 , customer_po_number , orig_sys_line_ref
928 , ordered_quantity , order_quantity_uom
929 , request_date , orig_sys_shipment_ref
930 , org_id , request_id
931 , change_sequence , sold_to_org_id
932 , customer_line_number , customer_shipment_number
933 FROM oe_lines_interface
934 WHERE order_source_id = p_order_source_id
935 AND orig_sys_document_ref = p_orig_sys_document_ref
936 AND decode(l_customer_key_profile, 'Y',
937 nvl(sold_to_org_id, -999), 1)
938 = decode(l_customer_key_profile, 'Y',
939 nvl(p_sold_to_org_id, -999), 1)
940 AND nvl(change_sequence, ' ')
941 = nvl(p_change_sequence, ' ')
942 AND nvl(request_id, -999)
943 = nvl(p_request_id, -999)
944 AND xml_transaction_type_code = p_xml_transaction_type_code
945 -- FOR UPDATE NOWAIT
946 ORDER BY orig_sys_line_ref, orig_sys_shipment_ref;
947
948 --
949 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
950 --
951 BEGIN
952 IF l_debug_level > 0 THEN
953 oe_debug_pub.add( 'START OF QUERY_INF_RECORDS' ) ;
954 END IF;
955
956
957
958 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
959 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
960 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
961 IF l_debug_level > 0 THEN
962 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
963 END IF;
964 End If;
965
966 IF l_debug_level > 0 THEN
967 oe_debug_pub.add('Key Information');
968 oe_debug_pub.add('Order Source Id:' || p_order_source_id);
969 oe_debug_pub.add( 'Orig Sys Document Ref:' || p_orig_sys_document_ref);
970 oe_debug_pub.add( 'Sold To Org Id:' || p_sold_to_org_id);
971 oe_debug_pub.add( 'Change Sequence:' || p_change_sequence);
972 oe_debug_pub.add( 'Request Id:' || p_request_id);
973 oe_debug_pub.add( 'XML Transaction Type Code:' || p_xml_transaction_type_code);
974 END IF;
975 -- { Start of header fetch
976 --HEADER-----------HEADER---------------HEADER----------------HEADER---------
977
978 OPEN l_header_cursor;
979 FETCH l_header_cursor
980 INTO
981 l_header_rec.order_source_id, l_header_rec.orig_sys_document_ref
982 , l_header_rec.change_sequence, l_header_rec.booked_flag
983 , l_header_val_rec.customer_number, l_header_rec.cust_po_number
984 , l_header_rec.freight_terms_code, l_header_val_rec.freight_terms
985 , l_header_rec.fob_point_code, l_header_val_rec.fob_point
986 , l_header_rec.invoice_to_org_id, l_header_val_rec.invoice_to_org
987 , l_header_val_rec.invoice_to_address1, l_header_val_rec.invoice_to_address2
988 , l_header_val_rec.invoice_to_address3, l_header_val_rec.invoice_to_city
989 , l_header_val_rec.invoice_to_state, l_header_val_rec.invoice_to_zip
990 , l_header_val_rec.invoice_to_county, l_header_val_rec.invoice_to_country
991 , l_header_rec.ship_from_org_id, l_header_val_rec.ship_from_org
992 , l_header_rec.ship_to_org_id, l_header_val_rec.ship_to_org
993 , l_header_val_rec.ship_to_address1, l_header_val_rec.ship_to_address2
994 , l_header_val_rec.ship_to_address3, l_header_val_rec.ship_to_city
995 , l_header_val_rec.ship_to_state, l_header_val_rec.ship_to_zip
996 , l_header_val_rec.ship_to_county, l_header_val_rec.ship_to_country
997 , l_header_rec.sold_to_org_id, l_header_val_rec.sold_to_org
998 , l_header_rec.org_id, l_header_rec.request_id
999 , l_header_rec.xml_message_id, l_header_val_rec.payment_term
1000 ;
1001
1002 l_order_source_id := l_header_rec.order_source_id;
1003 l_orig_sys_document_ref := l_header_rec.orig_sys_document_ref;
1004 l_change_sequence := l_header_rec.change_sequence;
1005
1006 IF l_debug_level > 0 THEN
1007 oe_debug_pub.add( 'ORDER SOURCE ID: ' || L_ORDER_SOURCE_ID ) ;
1008 oe_debug_pub.add( 'ORIG SYS REFERENCE: '|| L_ORIG_SYS_DOCUMENT_REF ) ;
1009 oe_debug_pub.add( 'CHANGE SEQUENCE: ' || L_CHANGE_SEQUENCE ) ;
1010 END IF;
1011
1012 -- { Start of line fetch
1013 --LINE---------------------LINE-----------------LINE----------------LINE-----
1014 IF l_debug_level > 0 THEN
1015 oe_debug_pub.add( 'BEFORE LINES LOOP' ) ;
1016 END IF;
1017
1018 l_line_count := 0;
1019
1020 OPEN l_line_cursor;
1021 LOOP
1022 FETCH l_line_cursor
1023 INTO
1024 l_line_rec.order_source_id, l_line_rec.orig_sys_document_ref
1025 , l_line_rec.ordered_item, l_line_rec.ordered_item_id
1026 , l_line_rec.cust_po_number, l_line_rec.orig_sys_line_ref
1027 , l_line_rec.ordered_quantity, l_line_rec.order_quantity_uom
1028 , l_line_rec.request_date, l_line_rec.orig_sys_shipment_ref
1029 , l_line_rec.org_id, l_line_rec.request_id
1030 , l_line_rec.change_sequence, l_line_rec.sold_to_org_id
1031 , l_line_rec.customer_line_number, l_line_rec.customer_shipment_number
1032 ;
1033 EXIT WHEN l_line_cursor%NOTFOUND;
1034
1035 ---------------- <Increase the Record Counter> --------------------------
1036 l_line_count := l_line_count + 1;
1037 ---------------- </Increase the Record Counter> -------------------------
1038 IF l_debug_level > 0 THEN
1039 oe_debug_pub.add( 'ASSIGNING RECORD TO LINE TABLE....LINE COUNT = ' || L_LINE_COUNT ) ;
1040 END IF;
1041 ---------------- <Assign record to line table> --------------------------
1042 l_line_tbl(l_line_count) := l_line_rec;
1043 ---------------- </Assign record to line table> -------------------------
1044
1045 END LOOP;
1046 CLOSE l_line_cursor;
1047 --LINE---------------------LINE-----------------LINE----------------LINE-----
1048 -- End of line fetch}
1049
1050 CLOSE l_header_cursor;
1051 --HEADER-----------HEADER---------------HEADER----------------HEADER---------
1052 -- End of Header fetch}
1053
1054
1055 ------------------ <Assign data to out variable> --------------------------
1056 x_header_rec := l_header_rec;
1057 x_header_val_rec := l_header_val_rec;
1058 x_line_tbl := l_line_tbl;
1059 ------------------ </Assign data to out variable> -------------------------
1060
1061 IF l_debug_level > 0 THEN
1062 oe_debug_pub.add( 'END OF QUERY_INF_RECORDS' ) ;
1063 END IF;
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 IF l_debug_level > 0 THEN
1067 oe_debug_pub.add ('In others exception in query inf:' || SQLERRM);
1068 END IF;
1069 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1070 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'query_inf_records');
1071 END IF;
1072 END Query_Inf_Records;
1073 -- End of procedure Query Interface Records }
1074
1075
1076 PROCEDURE Process_Xml_Acknowledgment
1077 ( p_api_version_number IN NUMBER := 1,
1078 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1079 p_order_source_id IN NUMBER := G_XML_ORDER_SOURCE_ID,
1080 p_orig_sys_document_ref IN VARCHAR2,
1081 p_sold_to_org_id IN NUMBER,
1082 p_change_sequence IN VARCHAR2,
1083 p_header_id IN NUMBER := NULL,
1084 p_line_id IN NUMBER := NULL,
1085 p_msg_id IN NUMBER := NULL,
1086 p_request_id IN NUMBER := NULL,
1087 p_itemtype IN VARCHAR2 := OE_ORDER_IMPORT_WF.G_WFI_ORDER_ACK,
1088 p_start_from_flow IN VARCHAR2 := OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT,
1089 p_transaction_type IN VARCHAR2 := NULL,
1090 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1091 )
1092 IS
1093
1094
1095 -- (iii) Delete_row need not to be changed, discuss with Sameer.
1096
1097 -- (iv) Make sure raise event is raised only for the Xml order source
1098
1099 -- (v) Code to check if the acknowledgment is setup for the TP
1100
1101 l_header_rec OE_Order_Pub.Header_Rec_Type;
1102 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type;
1103 l_line_rec OE_Order_Pub.Line_Rec_Type;
1104 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
1105 l_line_val_rec OE_Order_Pub.Line_Val_Rec_Type;
1106 l_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type;
1107 l_header_id Number;
1108 l_header_last_ack_code Varchar2(30);
1109 l_reject_order Varchar2(1) := 'Y';
1110 l_return_status Varchar2(1) := fnd_api.g_ret_sts_success;
1111 l_line_id Number;
1112 l_line_ack_id Number;
1113 l_line_last_ack_code Varchar2(30);
1114 l_ind_cntr Number;
1115 l_acknowledgment_type Varchar2(6);
1116 l_orig_sys_document_ref Varchar2(50);
1117 l_request_id Number := p_request_id;
1118 l_cancelled_flag Varchar2(1);
1119 i pls_integer;
1120 j pls_integer;
1121 k pls_integer;
1122 l_customer_key_profile Varchar2(1) := 'N';
1123 l_hold_result VARCHAR2(30);
1124 l_hold_id NUMBER := 56;
1125 l_msg_count NUMBER := 0;
1126 l_msg_data VARCHAR2(2000);
1127
1128 Cursor Line_Ack_Cur Is
1129 Select Line_Id
1130 From oe_line_acks
1131 Where header_id = l_header_id
1132 And acknowledgment_type = l_acknowledgment_type
1133 And decode(l_customer_key_profile, 'Y',
1134 nvl(sold_to_org_id, -999), 1)
1135 = decode(l_customer_key_profile, 'Y',
1136 nvl(p_sold_to_org_id, -999), 1);
1137
1138 Cursor Cancel_Line_Ack_Cur Is
1139 Select Line_Id
1140 From oe_order_lines
1141 Where request_id = l_request_id
1142 And header_id = l_header_id
1143 And decode(l_customer_key_profile, 'Y',
1144 nvl(sold_to_org_id, -999), 1)
1145 = decode(l_customer_key_profile, 'Y',
1146 nvl(p_sold_to_org_id, -999), 1);
1147
1148 Cursor SSO_Line_Ack_Cur Is
1149 Select Line_Id, Last_Ack_Code
1150 From oe_line_acks
1151 Where header_id = l_header_id
1152 And acknowledgment_type = l_acknowledgment_type
1153 And request_id = l_request_id
1154 And decode(l_customer_key_profile, 'Y',
1155 nvl(sold_to_org_id, -999), 1)
1156 = decode(l_customer_key_profile, 'Y',
1157 nvl(p_sold_to_org_id, -999), 1);
1158
1159 --
1160 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1161 --
1162 BEGIN
1163
1164 -- { Check what parameters are passed
1165 -- If Data has to be extracted from interface table p_header_id
1166 -- should be null (as order never got created), otherwise use
1167 -- header_id to query the data from the base tables
1168
1169 IF l_debug_level > 0 THEN
1170 oe_debug_pub.add( 'OEXPACKB: ENTERING PROCESS_XML_ACKNOWLEDGMENT' ) ;
1171 END IF;
1172 l_acknowledgment_type := p_transaction_type;
1173
1174 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1175 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
1176 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
1177 IF l_debug_level > 0 THEN
1178 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
1179 END IF;
1180 End If;
1181
1182 IF l_debug_level > 0 THEN
1183 oe_debug_pub.add( 'request_id passed in:' || l_request_id ) ;
1184 END IF;
1185
1186 --If transaction is 3a6, the order has already been accepted.
1187 If p_transaction_type = G_TRANSACTION_SSO or p_transaction_type = G_TRANSACTION_CSO then
1188 l_reject_order := 'N';
1189 End If;
1190
1191 -- { Start of p_header_id is null
1192 If p_header_id is NULL Then
1193
1194 IF l_debug_level > 0 THEN
1195 oe_debug_pub.add( 'OEXPACKB: HEADER ID IS NULL' ) ;
1196 END IF;
1197 -- We need to check here to make sure if the order is created or rejected
1198 Begin
1199 Select orig_sys_document_ref
1200 into l_orig_sys_document_ref
1201 From oe_headers_interface
1202 Where order_source_id = G_XML_ORDER_SOURCE_ID
1203 And orig_sys_document_ref = p_orig_sys_document_ref
1204 And decode(l_customer_key_profile, 'Y',
1205 nvl(sold_to_org_id, -999), 1)
1206 = decode(l_customer_key_profile, 'Y',
1207 nvl(p_sold_to_org_id, -999), 1)
1208 AND nvl(change_sequence, ' ')
1209 = nvl(p_change_sequence, ' ')
1210 And xml_transaction_type_code = p_transaction_type
1211 And request_id = p_request_id;
1212 -- change sequence should go here, so should customer.
1213
1214 IF l_debug_level > 0 THEN
1215 oe_debug_pub.add( 'OEXPACKB: GOT THE ORIG SYS DOCUMENT REF => ' || L_ORIG_SYS_DOCUMENT_REF ) ;
1216 END IF;
1217
1218 Exception
1219 When NO_DATA_FOUND Then
1220 -- This means we are accepting the order let us set the flag
1221 IF l_debug_level > 0 THEN
1222 oe_debug_pub.add( 'OEXPACKB: L_REJECT_ORDER IS SET TO N' ) ;
1223 END IF;
1224 l_reject_order := 'N';
1225 When OTHERS Then
1226 -- Code here to raise error as not able to find the orig_sys_document_ref
1227 IF l_debug_level > 0 THEN
1228 oe_debug_pub.add( 'OEXPACKB: OTHERS IN SELECT FROM OE_HEADERS_INTERFACE' ) ;
1229 END IF;
1230 fnd_message.set_name ('ONT', 'OE_OI_ACK_DATA_NOT_FOUND');
1231 fnd_message.set_token ('TABLE', 'oe_headers_interface');
1232 oe_msg_pub.add;
1233 End;
1234
1235
1236 -- { Start of l_reject_order = 'Y'
1237 If l_reject_order = 'Y' Then
1238 query_inf_records( p_order_source_id => p_order_source_id,
1239 p_orig_sys_document_ref => p_orig_sys_document_ref,
1240 p_sold_to_org_id => p_sold_to_org_id,
1241 p_change_sequence => p_change_sequence,
1242 p_msg_id => p_msg_id,
1243 p_request_id => p_request_id,
1244 p_xml_transaction_type_code => p_transaction_type,
1245 x_header_rec => l_header_rec,
1246 x_header_val_rec => l_header_val_rec,
1247 x_line_tbl => l_line_tbl,
1248 x_line_val_tbl => l_line_val_tbl);
1249
1250 Else
1251
1252 Begin
1253 Select header_id
1254 into l_header_id
1255 From oe_order_headers
1256 Where order_source_id = G_XML_ORDER_SOURCE_ID
1257 And orig_sys_document_ref = p_orig_sys_document_ref
1258 And decode(l_customer_key_profile, 'Y',
1259 nvl(sold_to_org_id, -999), 1)
1260 = decode(l_customer_key_profile, 'Y',
1261 nvl(p_sold_to_org_id, -999), 1);
1262 --removing because change_sequence isn't needed and could have changed before this processing takes place
1263 /* And nvl(change_sequence, ' ')
1264 = nvl(p_change_sequence, ' ');
1265 */
1266 -- start bug 4195533
1267 OE_MSG_PUB.update_msg_context(
1268 p_header_id => l_header_id
1269 );
1270 -- end bug 4195533
1271
1272 IF l_debug_level > 0 THEN
1273 oe_debug_pub.add( 'OEXPACKB: GOT THE HEADER ID => ' || L_HEADER_ID ) ;
1274 END IF;
1275
1276 Exception
1277 When OTHERS Then
1278 -- Code here to raise error as not able to find the header id
1279 IF l_debug_level > 0 THEN
1280 oe_debug_pub.add( 'OEXPACKB: OTHERS IN SELECT FROM OE_ORDER_HEADERS' ) ;
1281 END IF;
1282 fnd_message.set_name ('ONT', 'OE_OI_ACK_DATA_NOT_FOUND');
1283 fnd_message.set_token ('TABLE', 'oe_order_headers');
1284 oe_msg_pub.add;
1285 End;
1286
1287 End If;
1288 -- End of l_reject_order = 'Y'}
1289
1290 End If;
1291 -- End of p_header_id is null }
1292
1293 -- {If p_header_id is not null
1294 If p_header_id Is Not Null OR (l_header_id is not null AND l_reject_order = 'N') Then
1295
1296 if p_header_id is not null then
1297 l_header_id := p_header_id;
1298 end if;
1299
1300 --{Call Query Row procedure to get the Header Data from Base
1301 -- OE_ORDER_HEADERS_ALL table
1302 Begin
1303 oe_header_util.query_row ( p_header_id => l_header_id,
1304 x_header_rec => l_header_rec);
1305
1306 Begin
1307 If l_header_rec.payment_term_id Is Not Null Then
1308 l_header_val_rec.payment_term := OE_Id_To_Value.Payment_Term
1309 (p_payment_term_id => l_header_rec.payment_term_id);
1310
1311 End If;
1312 Exception
1313 When Others Then
1314 If l_debug_level > 0 THEN
1315 oe_debug_pub.add ('Error in deriving value for Payment Term ' || SQLERRM);
1316 End If;
1317 End;
1318
1319 --retrieving seeded header status in case of 3a6 generated from PROC
1320 If p_transaction_type = G_TRANSACTION_SSO Or p_transaction_type = G_TRANSACTION_CSO Then
1321
1322
1323 If p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_PROC Then
1324
1325 Select last_ack_code
1326 into l_header_last_ack_code
1327 from oe_header_acks
1328 where acknowledgment_type = p_transaction_type
1329 and header_id = l_header_id
1330 and request_id = l_request_id;
1331
1332 IF l_debug_level > 0 THEN
1333 oe_debug_pub.add( 'RETRIEVED HEADER LAST_ACK_CODE: ' || L_HEADER_LAST_ACK_CODE ) ;
1334 END IF;
1335 l_header_rec.last_ack_code := l_header_last_ack_code;
1336 Else
1337 -- 3a6 generated from concurrent program
1338 If ((l_header_rec.flow_status_code = 'CANCELLED') or
1339 (l_header_rec.flow_status_code = 'CLOSED')) then
1340 l_header_rec.last_ack_code := l_header_rec.flow_status_code;
1341 End If;
1342 End If;
1343
1344 End If;
1345
1346 Exception
1347 When Others Then
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;--FND_API.G_RET_STS_SUCCESS;
1349 IF l_debug_level > 0 THEN
1350 oe_debug_pub.add( 'QUERY FROM OE_ORDER_HEADERS_ALL TABLE FAILED. ACK. NOT SEND: ' || sqlerrm) ;
1351 END IF;
1352 fnd_message.set_name ('ONT', 'OE_OI_ACK_DATA_NOT_FOUND');
1353 fnd_message.set_token ('TABLE', 'oe_order_headers');
1354 oe_msg_pub.add;
1355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356 --RETURN;
1357 End;
1358 -- End Header query}
1359
1360 --{Call Query Row procedure to get the Line Data from Base
1361 -- OE_ORDER_LINES_ALL table
1362
1363 IF l_debug_level > 0 THEN
1364 oe_debug_pub.add( 'ORDER_SOURCE_ID' || L_HEADER_REC.ORDER_SOURCE_ID ) ;
1365 oe_debug_pub.add( 'P_START_FROM_FLOW' || P_START_FROM_FLOW ) ;
1366 oe_debug_pub.add( 'TRANSACTION_TYPE ' || P_TRANSACTION_TYPE ) ;
1367 END IF;
1368
1369 Begin
1370 If (p_start_from_flow = Oe_Globals.G_WFI_LIN And p_line_id Is Not Null)
1371 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT And p_transaction_type = G_TRANSACTION_POI)
1372 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_IMPORT_PGM And p_transaction_type = G_TRANSACTION_POI)
1373 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_CONC_PGM And p_transaction_type = G_TRANSACTION_SSO)
1374 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_CONC_PGM And p_transaction_type = G_TRANSACTION_CSO) Then
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add( 'IN IF STATEMENT' ) ;
1377 oe_debug_pub.add( P_START_FROM_FLOW || ' FLOW , WITH LINE_ID => ' || P_LINE_ID ) ;
1378 oe_debug_pub.add( 'TRANSACTION_TYPE ' || P_TRANSACTION_TYPE ) ;
1379 END IF;
1380 l_line_id := p_line_id;
1381 oe_line_util.query_rows ( p_header_id => l_header_id,
1382 p_line_id => l_line_id,
1383 x_line_tbl => l_line_tbl);
1384
1385 -- checking for 'CLOSED' or 'CANCELLED' line status for 3a6
1386
1387 If p_transaction_type = G_TRANSACTION_SSO Then
1388
1389 j := l_line_tbl.First;
1390 while j is not null loop
1391 if ((l_line_tbl(j).flow_status_code = 'CANCELLED') or
1392 (l_line_tbl(j).flow_status_code = 'CLOSED')) then
1393 l_line_tbl(j).last_ack_code := l_line_tbl(j).flow_status_code;
1394 end if;
1395 j := l_line_tbl.Next(j);
1396 end loop;
1397
1398 End If;
1399
1400 End If;
1401
1402 -- Start line query for 3A9
1403 -- Send all the lines if the Order is Cancelled or else send only the Partially cancelled lines
1404 -- We reuse same logic for 3a8 i.e. only ack back the changed lines, which are derived using the request id
1405 -- so we pick all lines which have the same request id as the header because we know these were changed last
1406 If (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT And p_transaction_type = G_TRANSACTION_CPO)
1407 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_IMPORT_PGM And p_transaction_type = G_TRANSACTION_CPO)
1408 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT And p_transaction_type = G_TRANSACTION_CHO)
1409 Or (p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_IMPORT_PGM And p_transaction_type = G_TRANSACTION_CHO) Then
1410 IF l_debug_level > 0 THEN
1411 oe_debug_pub.add( 'IN IF STATEMENT WITH TRANSACTION TYPE = '|| p_transaction_type ) ;
1412 END IF;
1413 Begin
1414 Select cancelled_flag
1415 Into l_cancelled_flag
1416 From oe_order_headers
1417 Where header_id = l_header_id;
1418 IF l_debug_level > 0 THEN
1419 oe_debug_pub.add( 'HEADER_ID: ' || L_HEADER_ID ) ;
1420 END IF;
1421
1422 If nvl(l_cancelled_flag, 'N') = 'N' Then
1423 l_ind_cntr := 0;
1424 Open Cancel_Line_Ack_Cur;
1425 Loop
1426 Fetch Cancel_Line_Ack_Cur
1427 Into l_line_ack_id;
1428 Exit When Cancel_Line_Ack_Cur%NOTFOUND;
1429 IF l_line_ack_id IS NOT NULL THEN -- bug 3363327
1430 IF l_debug_level > 0 THEN
1431 oe_debug_pub.add( 'FETCHED LINE ACK ID: ' || L_LINE_ACK_ID ) ;
1432 END IF;
1433
1434 l_line_rec := oe_line_util.query_row ( p_line_id => l_line_ack_id);
1435 END IF; -- end bug 3363327
1436
1437 l_ind_cntr := l_ind_cntr + 1;
1438 l_line_tbl(l_ind_cntr) := l_line_rec;
1439 End Loop;
1440 Close Cancel_Line_Ack_Cur;
1441 End If;
1442 Exception
1443 When Others Then
1444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --FND_API.G_RET_STS_SUCCESS;
1445 IF l_debug_level > 0 THEN
1446 oe_debug_pub.add( 'QUERY FROM OE_ORDER_LINES_ALL TABLE FAILED. ACK. NOT SEND' ) ;
1447 END IF;
1448 fnd_message.set_name ('ONT', 'OE_OI_ACK_DATA_NOT_FOUND');
1449 fnd_message.set_token ('TABLE', 'oe_order_lines');
1450 oe_msg_pub.add;
1451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452 --RETURN;
1453 End;
1454
1455 End If;
1456
1457 If p_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_PROC Then
1458 IF l_debug_level > 0 THEN
1459 oe_debug_pub.add( 'G_WFI_PROC FLOW' ) ;
1460 END IF;
1461 -- l_acknowledgment_type := G_TRANSACTION_SSO;
1462 l_ind_cntr := 0;
1463
1464 --get only lines for this request if this is for 3a6
1465 if p_transaction_type = G_TRANSACTION_SSO Or p_transaction_type = G_TRANSACTION_CSO then
1466 Open sso_line_ack_cur;
1467 Loop
1468 Fetch sso_line_ack_cur
1469 Into l_line_ack_id, l_line_last_ack_code;
1470 If l_line_ack_id is Not Null Then
1471 IF l_debug_level > 0 THEN
1472 oe_debug_pub.add( 'G_WFI_PROC FLOW , L_LINE_ACK_ID => ' || L_LINE_ACK_ID ) ;
1473 oe_debug_pub.add( 'RETRIEVED LINE LAST_ACK_CODE: ' || L_LINE_LAST_ACK_CODE ) ;
1474 END IF;
1475 l_line_rec :=
1476 oe_line_util.query_row ( p_line_id => l_line_ack_id);
1477 l_line_rec.last_ack_code := l_line_last_ack_code;
1478 End If;
1479 Exit When sso_line_ack_cur%notfound;
1480 l_ind_cntr := l_ind_cntr + 1;
1481 l_line_tbl(l_ind_cntr) := l_line_rec;
1482 End Loop;
1483 Close sso_line_ack_cur;
1484 else
1485 Open line_ack_cur;
1486 Loop
1487 Fetch line_ack_cur
1488 Into l_line_ack_id;
1489 If l_line_ack_id is Not Null Then
1490 IF l_debug_level > 0 THEN
1491 oe_debug_pub.add( 'G_WFI_PROC FLOW , L_LINE_ACK_ID => ' || L_LINE_ACK_ID ) ;
1492 END IF;
1493 l_line_rec :=
1494 oe_line_util.query_row ( p_line_id => l_line_ack_id);
1495 End If;
1496 Exit When line_ack_cur%notfound;
1497 l_ind_cntr := l_ind_cntr + 1;
1498 l_line_tbl(l_ind_cntr) := l_line_rec;
1499 End Loop;
1500 Close line_ack_cur;
1501 end if;
1502
1503 End If;
1504 Exception
1505 When Others Then
1506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;--FND_API.G_RET_STS_SUCCESS;
1507 IF l_debug_level > 0 THEN
1508 oe_debug_pub.add( 'QUERY FROM OE_ORDER_LINES_ALL TABLE FAILED. ACK. NOT SEND' ) ;
1509 END IF;
1510 fnd_message.set_name ('ONT', 'OE_OI_ACK_DATA_NOT_FOUND');
1511 fnd_message.set_token ('TABLE', 'oe_order_lines');
1512 oe_msg_pub.add;
1513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1514 --RETURN;
1515 End;
1516 -- End Line query}
1517
1518 End If;
1519 -- End of p_header_id is not null}
1520
1521 --begin setting request_id to eventkey for 3a6 to allow unique identification in acks tables;
1522 --now also sets change sequence because passed value from workflow should
1523 --sync with the value inserted into the acks table since a change sequence
1524 --pulled from the base table could change in the interim
1525
1526 if p_transaction_type = G_TRANSACTION_SSO Or p_transaction_type = G_TRANSACTION_CSO then
1527 l_header_rec.request_id := l_request_id;
1528 l_header_rec.change_sequence := p_change_sequence;
1529
1530 i := l_line_tbl.First;
1531 while i is not null loop
1532 l_line_tbl(i).request_id := l_request_id;
1533 l_line_tbl(i).change_sequence := p_change_sequence;
1534 i := l_line_tbl.Next(i);
1535 end loop;
1536 --end of setting request id and change sequence for 3a6
1537 else
1538 --set only change sequence for other transaction types
1539 l_header_rec.change_sequence := p_change_sequence;
1540
1541 i := l_line_tbl.First;
1542 while i is not null loop
1543 l_line_tbl(i).change_sequence := p_change_sequence;
1544 i := l_line_tbl.Next(i);
1545 end loop;
1546
1547 end if;
1548
1549
1550 OE_Header_Ack_Util.Insert_Row
1551 ( p_header_rec => l_header_rec
1552 , p_header_val_rec => l_header_val_rec
1553 , p_old_header_rec => l_header_rec
1554 , p_old_header_val_rec => l_header_val_rec
1555 , p_reject_order => l_reject_order
1556 , p_ack_type => l_acknowledgment_type
1557 , x_return_status => l_return_status
1558 );
1559 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
1560 raise fnd_api.g_exc_unexpected_error;
1561 Elsif l_return_status = fnd_api.g_ret_sts_error Then
1562 raise fnd_api.g_exc_error;
1563 End If;
1564 -- End of Header data insert into ack header table}
1565
1566 -- { Start of Line data insert into ack header table
1567 OE_Line_Ack_Util.Insert_Row
1568 ( p_line_tbl => l_line_tbl
1569 , p_line_val_tbl => l_line_val_tbl
1570 , p_old_line_tbl => l_line_tbl
1571 , p_old_line_val_tbl => l_line_val_tbl
1572 , p_buyer_seller_flag => 'B'
1573 , p_reject_order => l_reject_order
1574 , p_ack_type => l_acknowledgment_type
1575 , x_return_status => l_return_status
1576 );
1577 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
1578 raise fnd_api.g_exc_unexpected_error;
1579 Elsif l_return_status = fnd_api.g_ret_sts_error Then
1580 raise fnd_api.g_exc_error;
1581 End If;
1582 -- End of Line data insert into ack header table}
1583
1584 END Process_Xml_Acknowledgment;
1585
1586 -- { Start of the Is_Delivery_Required
1587 -- This api will do following
1588 -- 1. For the given customer_id, transaction_type and subtype
1589 -- 2. Get the party_id, party_site_id of Usage of type 'SOLD_TO' that is primary
1590 -- and active
1591 -- 3. If exists then call ecx api isDeliveryRequired to validate for
1592 -- the transaction and customer site combination.
1593 Procedure Is_Delivery_Required
1594 ( p_customer_id in number,
1595 p_transaction_type in varchar2,
1596 p_transaction_subtype in varchar2,
1597 p_org_id in number,
1598 x_party_id Out NOCOPY /* file.sql.39 change */ Number,
1599 x_party_site_id Out NOCOPY /* file.sql.39 change */ Number,
1600 x_is_delivery_required out NOCOPY /* file.sql.39 change */ varchar2,
1601 x_return_status out NOCOPY /* file.sql.39 change */ varchar2
1602 )
1603 Is
1604
1605 l_party_id number;
1606 l_party_site_id number;
1607 l_cust_acct_site_id number;
1608 l_retcode pls_integer;
1609 l_errmsg varchar2(2000);
1610 l_result boolean := FALSE;
1611 l_org_id Number := p_org_id;
1612 --
1613 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1614 --
1615 Begin
1616
1617 IF l_debug_level > 0 THEN
1618 oe_debug_pub.add( 'OEXPACKB: ENTERING IS_DELIVERY_REQUIRED' ) ;
1619 END IF;
1620
1621 x_is_delivery_required := 'N';
1622 If p_org_id IS NULL Then
1623 l_org_id := MO_GLOBAL.Get_Current_Org_Id;
1624 End If;
1625 -- { Start step 1 and 2
1626 -- Select the party_id and party_side id for the
1627 Select /* MOAC_SQL_CHANGE */ a.cust_acct_site_id, a.party_site_id, c.party_id
1628 Into l_cust_acct_site_id, l_party_site_id, l_party_id
1629 From hz_cust_acct_sites_all a, hz_cust_site_uses_all b, hz_cust_accounts c
1630 Where a.cust_acct_site_id = b.cust_acct_site_id
1631 And a.cust_account_id = p_customer_id
1632 And a.cust_account_id = c.cust_account_id
1633 /* And NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),
1634 1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
1635 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
1636 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) */
1637 And a.org_id = l_org_id
1638 And b.site_use_code = 'SOLD_TO'
1639 And b.primary_flag = 'Y'
1640 And b.status = 'A'
1641 And a.status ='A'; --bug 2752321
1642
1643 IF l_debug_level > 0 THEN
1644 oe_debug_pub.add( 'L_CUST_ACC ' || L_CUST_ACCT_SITE_ID ) ;
1645 oe_debug_pub.add( 'L_PARTY_ID ' || L_PARTY_ID ) ;
1646 oe_debug_pub.add( 'L_PARTY_SITE_ID ' || L_PARTY_SITE_ID ) ;
1647 oe_debug_pub.add( 'BEFORE CALL TO ISDELIVERY REQ' ) ;
1648 END IF;
1649 ecx_document.isDeliveryRequired
1650 (
1651 transaction_type => p_transaction_type,
1652 transaction_subtype => p_transaction_subtype,
1653 party_id => l_party_id,
1654 party_site_id => l_party_site_id,
1655 resultout => l_result,
1656 retcode => l_retcode,
1657 errmsg => l_errmsg
1658 );
1659 IF l_debug_level > 0 THEN
1660 oe_debug_pub.add( 'AFTER CALL TO ISDELIVERY REQ ' || L_ERRMSG ) ;
1661 END IF;
1662
1663 IF (l_result) THEN
1664 x_is_delivery_required := 'Y';
1665 x_party_site_id := l_party_site_id;
1666 x_party_id := l_party_id;
1667 IF l_debug_level > 0 THEN
1668 oe_debug_pub.add( 'IS DELIVERY REQUIRED' || X_IS_DELIVERY_REQUIRED ) ;
1669 END IF;
1670 ELSE
1671 x_is_delivery_required := 'N';
1672 x_party_site_id := l_party_site_id;
1673 x_party_id := l_party_id;
1674 IF l_debug_level > 0 THEN
1675 oe_debug_pub.add( 'IS DELIVERY REQUIRED' || X_IS_DELIVERY_REQUIRED ) ;
1676 END IF;
1677 END IF;
1678
1679 x_return_status := FND_API.G_RET_STS_SUCCESS;
1680 IF l_debug_level > 0 THEN
1681 oe_debug_pub.add( 'OEXPACKB: EXITING IS_DELIVERY_REQUIRED' ) ;
1682 END IF;
1683 EXCEPTION
1684 WHEN NO_DATA_FOUND THEN
1685 IF l_debug_level > 0 THEN
1686 oe_debug_pub.add( 'OEXPACKB: EXITING IS_DELIVERY_REQUIRED WITH NO_DATA_FOUND' ) ;
1687 END IF;
1688 x_return_status := FND_API.G_RET_STS_ERROR;
1689 -- start bug 3711152
1690 -- fnd_message.set_name ('ONT', 'OE_OI_TP_NOT_FOUND');
1691 -- fnd_message.set_token ('CUST_ID', p_customer_id);
1692 -- oe_msg_pub.add;
1693 -- end bug 3711152
1694 WHEN OTHERS THEN
1695 IF l_debug_level > 0 THEN
1696 oe_debug_pub.add( 'OEXPACKB: EXITING IS_DELIVERY_REQUIRED WITH OTHERS' ) ;
1697 END IF;
1698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1699 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1700 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Is_Delivery_Required');
1701 END IF;
1702 IF l_debug_level > 0 THEN
1703 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1704 END IF;
1705
1706 End Is_Delivery_Required;
1707 -- End of the Is_Delivery_Required }
1708
1709 -- { Start of the Process_SSO
1710 -- This api will do following
1711 -- 1. Check to see if the Customer is a TP and required 3A6
1712 -- IF 'NO' Then Exit out of the procedure without doing anything else
1713 -- IF 'YES' then go to step 2.
1714 -- 2. Check for the columns change which can trigger the sending of the
1715 -- 3A6.
1716 -- 2a. Check at the header record if any relevant column change then set the
1717 -- Flag that Header record need to be send.
1718 -- 2b. Loop thru the Line table to check for the line level relevant change and
1719 -- Keep on Inserting that records Line_Id in the Acknowledgment table, with
1720 -- the status information because at this point we know what is the reason
1721 -- for this information sending. Also Set the Flag that the Line Information
1722 -- is inserted.
1723 -- 3. Check if flag related to Header information needed to be inserted is
1724 -- there or Line inserted flag is there then insert the header_id too in
1725 -- the header ack table, as you can not send line without header even nothing
1726 -- changed for the header.
1727 -- 4. ....
1728 PROCEDURE Process_SSO
1729 ( p_api_version_number IN NUMBER :=1, -- GENESIS
1730 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1731 p_header_rec IN OE_Order_Pub.Header_Rec_Type,
1732 p_line_tbl IN OE_Order_Pub.Line_Tbl_Type,
1733 p_old_header_rec IN OE_Order_Pub.Header_Rec_Type,
1734 p_old_line_tbl IN OE_Order_Pub.Line_Tbl_Type,
1735 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1736 )
1737 IS
1738 l_is_delivery_required varchar2(1);
1739 l_party_id Number;
1740 l_party_site_id Number;
1741 l_header_req varchar2(1);
1742 l_line_req varchar2(1);
1743 l_change_usp varchar2(1);
1744 l_change_oqty varchar2(1);
1745 l_change_sadt varchar2(1);
1746 l_change_sqty varchar2(1);
1747 l_change_ssdt varchar2(1);
1748 l_change_uom varchar2(1);
1749
1750 -- GENESIS --
1751 l_change_fsc varchar2(1);
1752 l_sync_header VARCHAR2(1); -- := 'N';
1753 l_sync_line VARCHAR2(1); -- := 'N';
1754 l_insert_sync_line VARCHAR2(1); -- := 'N';
1755 l_change_type VARCHAR2(30); -- := NULL;
1756 l_ack_type VARCHAR2(30); -- := NULL;
1757 l_sync_header_id NUMBER;
1758 l_sync_line_id NUMBER;
1759 l_hdr_req_id NUMBER;
1760 l_lin_req_id NUMBER;
1761 -- GENESIS --
1762
1763 l_header_status varchar2(30);
1764 l_header_status_cso varchar2(30);
1765 l_line_status varchar2(30);
1766 l_line_rec OE_Order_Pub.Line_Rec_Type;
1767 i pls_integer;
1768 j pls_integer;
1769 l_return_status varchar2(1);
1770 l_itemkey_sso number;
1771 l_itemkey_cso number;
1772 l_header_rec OE_Order_Pub.Header_Rec_Type;
1773 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
1774 l_is_delivery_required_cso varchar2(1) := 'N'; -- initialized so that if the code release level
1775 -- is below 110510, then the code behaves
1776 -- exactly the same as when 3A7 transaction is not set up at all
1777 l_change_usp_cso varchar2(1); -- variables to detect when 3a7 is to be generated
1778 l_change_ssdt_cso varchar2(1);
1779 l_change_oqty_cso varchar2(1);
1780 l_change_uom_cso varchar2(1);
1781 l_header_req_cso varchar2(1);
1782 l_line_req_cso varchar2(1);
1783 l_line_status_cso varchar2(30);
1784 l_hold_result VARCHAR2(30);
1785 l_hold_id NUMBER := 56;
1786 l_msg_count NUMBER := 0;
1787 l_msg_data VARCHAR2(2000);
1788 l_cso_response_profile varchar2(1);
1789 l_apply_3a7_hold boolean := FALSE;
1790 l_3a7_buyer_line boolean := FALSE;
1791 l_line_exists VARCHAR2(10) := 'N'; -- Bug 9685021
1792
1793 -- Bug 13008311
1794 l_lin_chk VARCHAR2(1) := 'N';
1795 -- Bug 13008311
1796
1797 l_sync_line_inserted boolean := false; --Bug# 12879272
1798 --
1799 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1800 --
1801 Begin
1802
1803 l_header_rec := p_header_rec;
1804 l_line_tbl := p_line_tbl;
1805
1806 select OE_XML_MESSAGE_SEQ_S.nextval
1807 into l_itemkey_sso
1808 from dual;
1809
1810 select OE_XML_MESSAGE_SEQ_S.nextval
1811 into l_itemkey_cso
1812 from dual;
1813
1814 -- no longer needed, and we also are using the actual request_id
1815 -- to detect which lines were changed by the buyer for 3a7
1816
1817 /* l_header_rec.request_id := l_itemkey_sso;
1818
1819 k := l_line_tbl.First;
1820 while k is not null loop
1821 l_line_tbl(k).request_id := l_itemkey_sso;
1822 k := l_line_tbl.Next(k);
1823 end loop;*/
1824
1825 IF l_debug_level > 0 THEN
1826 oe_debug_pub.add( 'OEXPACKB: ENTERING PROCESS_SSO, itemkeys are ' || l_itemkey_sso || ' and ' || l_itemkey_cso ) ;
1827 END IF;
1828
1829 IF NOT(OE_GENESIS_UTIL.source_aia_enabled(p_header_rec.order_source_id)) THEN -- GENESIS
1830 -- { Start step 1.
1831 -- Call the is_delivery_required api
1832 Is_Delivery_Required( p_customer_id => l_header_rec.sold_to_org_id,
1833 p_transaction_type => 'ONT',
1834 p_transaction_subtype => G_TRANSACTION_SSO,
1835 p_org_id => p_header_rec.org_id,
1836 x_party_id => l_party_id,
1837 x_party_site_id => l_party_site_id,
1838 x_is_delivery_required => l_is_delivery_required,
1839 x_return_status => l_return_status
1840 );
1841
1842 If (OE_Code_Control.code_release_level >= '110510') Then
1843
1844 Is_Delivery_Required( p_customer_id => l_header_rec.sold_to_org_id,
1845 p_transaction_type => 'ONT',
1846 p_transaction_subtype => G_TRANSACTION_CSO,
1847 p_org_id => p_header_rec.org_id,
1848 x_party_id => l_party_id,
1849 x_party_site_id => l_party_site_id,
1850 x_is_delivery_required => l_is_delivery_required_cso,
1851 x_return_status => l_return_status
1852 );
1853 IF l_is_delivery_required_cso = 'Y' THEN
1854 -- populate profile only if 3a7 is set up, this will need to change if we start
1855 -- supporting holds via 3a6 also
1856 l_cso_response_profile := nvl(FND_PROFILE.VALUE ('ONT_3A7_RESPONSE_REQUIRED'), 'N');
1857 END IF;
1858 IF l_debug_level > 0 THEN
1859 oe_debug_pub.add( 'OEXPACKB: is delivery required for 3a7 = ' || l_is_delivery_required_cso ) ;
1860 oe_debug_pub.add( 'OEXPACKB: fetched CSO Response Required profile ' || l_cso_response_profile) ;
1861 END IF;
1862
1863 End If;
1864
1865 If l_is_delivery_required = 'N' And
1866 l_is_delivery_required_cso = 'N' AND
1867 NOT(OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN -- GENESIS
1868 -- No delivery is required so return, no further processing is required
1869 IF l_debug_level > 0 THEN
1870 oe_debug_pub.add( 'OEXPACKB: NO DELIVERY IS REQUIRED SO RETURN' ) ;
1871 END IF;
1872 x_return_status := FND_API.G_RET_STS_SUCCESS;
1873 return;
1874 end if;
1875 END IF; -- Order source Id
1876
1877
1878 IF l_debug_level > 0 THEN
1879 oe_debug_pub.add( 'PRINTING HEADER RECORD VALUES' ) ;
1880 oe_debug_pub.add( 'OLD HEADER RECORD:' ) ;
1881 oe_debug_pub.add( 'orig_sys_document_ref:' || p_old_header_rec.orig_sys_document_ref) ;
1882 oe_debug_pub.add( 'booked_flag:' || p_old_header_rec.booked_flag) ;
1883 oe_debug_pub.add( 'flow_status_code:' || p_old_header_rec.flow_status_code) ;
1884 oe_debug_pub.add( 'request_id:' || p_old_header_rec.request_id) ;
1885 oe_debug_pub.add( 'transaction type:' || p_old_header_rec.xml_transaction_type_code) ;
1886 oe_debug_pub.add( 'operation code' || p_old_header_rec.operation) ;
1887 oe_debug_pub.add( 'xml message id:' || p_old_header_rec.xml_message_id) ;
1888 oe_debug_pub.add( 'NEW HEADER RECORD:' ) ;
1889 oe_debug_pub.add( 'orig_sys_document_ref:' || p_header_rec.orig_sys_document_ref) ;
1890 oe_debug_pub.add( 'booked_flag:' || p_header_rec.booked_flag) ;
1891 oe_debug_pub.add( 'flow_status_code:' || p_header_rec.flow_status_code) ;
1892 oe_debug_pub.add( 'request_id:' || p_header_rec.request_id) ;
1893 oe_debug_pub.add( 'transaction type:' || p_header_rec.xml_transaction_type_code) ;
1894 oe_debug_pub.add( 'operation code:' || p_header_rec.operation) ;
1895 oe_debug_pub.add( 'xml message id:' || p_header_rec.xml_message_id) ;
1896 oe_debug_pub.add( 'OE_ORDER_UTIL.G_HEADER_REC:' ) ;
1897 oe_debug_pub.add( 'orig_sys_document_ref:' || OE_ORDER_UTIL.g_header_rec.orig_sys_document_ref) ;
1898 oe_debug_pub.add( 'booked_flag:' || OE_ORDER_UTIL.g_header_rec.booked_flag) ;
1899 oe_debug_pub.add( 'flow_status_code:' || OE_ORDER_UTIL.g_header_rec.flow_status_code) ;
1900 oe_debug_pub.add( 'request_id:' || OE_ORDER_UTIL.g_header_rec.request_id) ;
1901 oe_debug_pub.add( 'transaction type:' || OE_ORDER_UTIL.g_header_rec.xml_transaction_type_code) ;
1902 oe_debug_pub.add( 'operation code:' || OE_ORDER_UTIL.g_header_rec.operation) ;
1903 oe_debug_pub.add( 'xml message id:' || OE_ORDER_UTIL.g_header_rec.xml_message_id) ;
1904 END IF;
1905
1906 -- End step 1. }
1907
1908
1909 -- { Start step 2.
1910 -- Check for the columns which will trigger the 3A6, requirement
1911
1912 -- { Start step 2a.
1913 -- Check for the header data, right now only the flow_status_code change
1914 -- i.e., when order booked first time, we need to send the 3A6.
1915 -- let us check for that
1916
1917 IF l_debug_level > 0 THEN
1918 oe_debug_pub.add( 'P_OLD_HEADER_REC.FLOW_STATUS_CODE ' || p_old_header_rec.flow_status_code) ;
1919 oe_debug_pub.add( 'L_HEADER_REC.FLOW_STATUS_CODE ' || l_header_rec.flow_status_code) ;
1920 oe_debug_pub.add( 'OE_ORDER_UTIL.G_HEADER_REC.FLOW_STATUS_CODE ' || OE_ORDER_UTIL.g_header_rec.flow_status_code) ;
1921 END IF;
1922
1923 l_header_req := 'N';
1924 l_header_req_cso := 'N';
1925 if l_header_rec.flow_status_code = 'BOOKED' AND
1926 NOT(OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN -- GENESIS
1927 IF l_debug_level > 0 THEN
1928 oe_debug_pub.add( 'OEXPACKB: L_HEADER_REC.FLOW_STATUS_CODE = BOOKED' ) ;
1929 END IF;
1930 if nvl(p_old_header_rec.flow_status_code, 'BOOKED') <> 'BOOKED' And
1931 nvl(OE_ORDER_UTIL.g_header_rec.flow_status_code, 'N') = 'BOOKED' Then
1932 IF l_debug_level > 0 THEN
1933 oe_debug_pub.add( 'OEXPACKB: P_OLD_HEADER_REC.FLOW_STATUS_CODE <> BOOKED' ) ;
1934 END IF;
1935 -- This means that order is Booked right now, and this is the condidate
1936 -- for 3A6, set the flag to indicate that..
1937
1938 l_header_req := 'Y';
1939 -- set some code to indicate that this 3A6 required because of this
1940 -- change, we should be able to use last_ack_code. Discuss...further
1941
1942 -- 3A7 changes
1943 -- The logic is as follows:
1944 -- At this point we have determined that the order has just been booked
1945 -- (barring any notification issues). Thus, if the XML orders accept state profile
1946 -- is set to 'BOOKED' then we have a change from 'Pending' to 'Accept' and thus
1947 -- need to send a 3A7, otherwise we turn off 3a7
1948 IF l_is_delivery_required_cso = 'Y' THEN
1949 IF nvl(FND_PROFILE.VALUE('ONT_XML_ACCEPT_STATE'), 'ENTERED') = 'ENTERED' THEN
1950 l_header_req_cso := 'N'; -- i.e. 3a7 not sent on Booking if accept state is Entered,
1951 -- unless of course there are line_changes (handled later)
1952 ELSIF nvl(OE_GLOBALS.G_XML_TXN_CODE, G_TRANSACTION_CSO) = G_TRANSACTION_POI THEN
1953 l_header_req_cso := 'N'; -- i.e. don't send 3a7 if order is booked by buyer
1954 ELSE
1955 l_header_req_cso := 'Y';
1956 END IF;
1957 END IF;
1958
1959 IF l_debug_level > 0 THEN
1960 oe_debug_pub.add( 'OEXPACKB: L_HEADER_STATUS = BOOKED' ) ;
1961 oe_debug_pub.add( 'OEXPACKB: L_IS_DELIVERY_REQD_CSO = ' || l_is_delivery_required_cso ) ;
1962 END IF;
1963 l_header_status := 'BOOKED';
1964 l_header_status_cso := 'BOOKED';
1965 end if;
1966
1967 -- GENESIS --
1968 -- Commented out as a part of O2C 2.5 (8516700:R12.ONT.B).
1969 -- Replacement code made available immediately below.
1970 /*
1971 ELSIF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) AND
1972 l_header_rec.flow_status_code <> 'BOOKED' AND
1973 l_header_rec.flow_status_code = 'CLOSED' AND
1974 p_old_header_rec.flow_status_code <> l_header_rec.flow_status_code THEN
1975 */
1976 ELSIF
1977 (
1978 (Oe_Genesis_Util.Source_Aia_Enabled(l_header_rec.order_source_id))
1979 AND
1980 (p_old_header_rec.flow_status_code <> l_header_rec.flow_status_code)
1981 AND
1982 (Oe_Genesis_Util.Status_Needs_Sync(l_header_rec.flow_status_code))
1983 )
1984 THEN
1985
1986 l_header_req := 'Y';
1987 l_header_status := l_header_rec.flow_status_code;
1988 l_sync_header := 'Y';
1989
1990 IF l_debug_level > 0 THEN
1991 oe_debug_pub.add( 'Genesis: Set header flag for flow status code change');
1992 oe_debug_pub.add( 'Genesis: Order Source Id = '||l_header_rec.order_source_id);
1993 oe_debug_pub.add( 'Genesis: l_header_req = '||l_header_req);
1994 oe_debug_pub.add( 'Genesis: l_header_status = '||l_header_status);
1995 oe_debug_pub.add( 'Genesis: l_sync_header = '||l_sync_header);
1996 END IF;
1997 -- GENESIS --
1998 end if;
1999 -- End step 2a. }
2000
2001 -- { Start step 2b.
2002 -- Check for the line data, unit_selling_price, ordered_qty,
2003 -- schedule_arrival_date, shipped_qty (that will go as status change 'SHIPPED'
2004 -- schedule_ship_date (this will also go as status change 'SCHEDULED'
2005 -- we need to send the 3A6 for above cases
2006 -- let us check for that
2007
2008 IF l_debug_level > 0 THEN
2009 oe_debug_pub.add( 'OEXPACKB: BEFORE THE WHILE LOOP' ) ;
2010 END IF;
2011 -- Following For loop is changed to while because of new Notify_OC change
2012 -- for i in 1..l_line_tbl.count
2013 -- loop
2014 i := l_line_tbl.First;
2015 while i is not null loop
2016 IF l_debug_level > 0 THEN
2017 oe_debug_pub.add( 'OEXPACKB: INSIDE THE FOR LOOP FOR I = ' || I ) ;
2018 END IF;
2019 -- resetting whether or not a particular line is required
2020 -- and whether attributes have changed. added 11/12/02 -jjmcfarl
2021 l_line_req := 'N';
2022 l_change_usp := 'N';
2023 l_change_oqty := 'N';
2024 l_change_sadt := 'N';
2025 l_change_sqty := 'N';
2026 l_change_ssdt := 'N';
2027 l_change_uom := 'N';
2028 l_change_fsc := 'N'; -- GENESIS --
2029 l_change_usp_cso := 'N';
2030 l_change_oqty_cso := 'N';
2031 l_change_uom_cso := 'N';
2032 l_change_ssdt_cso := 'N';
2033 l_line_req_cso := 'N';
2034 l_apply_3a7_hold := FALSE;
2035 l_3a7_buyer_line := FALSE;
2036 l_sync_line_inserted := FALSE; --Bug# 12879272
2037
2038 IF l_debug_level > 0 THEN
2039 oe_debug_pub.add( 'tested version line_id: ' || l_line_tbl(i).line_id);
2040 oe_debug_pub.add( 'passed in version line_id: ' || p_line_tbl(i).line_id);
2041 oe_debug_pub.add( 'old version line_id: ' || p_old_line_tbl(i).line_id);
2042 END IF;
2043
2044 -- moved the derivation of the j index outside of the UPDATE if-statement that
2045 -- follows. this is done so that j will be derived for both update and create
2046 -- operation codes, as it is needed to detect creates. --jjmcfarl
2047 if l_line_tbl(i).line_id = p_old_line_tbl(i).line_id then
2048 j := i;
2049 else
2050 j := get_line_index(p_old_line_tbl, l_line_tbl(i).line_id);
2051 IF l_debug_level > 0 THEN
2052 oe_debug_pub.add( 'got line index - test1: ' || j);
2053 END IF;
2054 end if;
2055
2056 -- { Start for the comparision when the operation on the Line table
2057 -- is UPDATE
2058 -- Bug 13008311 : Start
2059 -- if l_line_tbl(i).operation = Oe_Globals.G_OPR_UPDATE then
2060 l_lin_chk := 'N';
2061 IF j <> 0 THEN
2062 is_line_exists(p_line_tbl(j).line_id, l_lin_chk);
2063 END IF;
2064
2065 IF ( ( (l_line_tbl(i).operation IS NULL OR l_line_tbl(i).operation = FND_API.G_MISS_CHAR)
2066 AND l_lin_chk = 'Y')
2067 OR (l_line_tbl(i).operation = Oe_Globals.G_OPR_UPDATE)
2068 )
2069 THEN
2070 -- Bug 13008311 : End
2071
2072 IF l_debug_level > 0 THEN
2073 oe_debug_pub.add( 'OEXPACKB: LINE OPERATIONS IS UPDATE' ) ;
2074 END IF;
2075
2076 IF l_debug_level > 0 THEN
2077 oe_debug_pub.add( 'PRINTING LINE RECORD VALUES' ) ;
2078 oe_debug_pub.add( 'OLD LINE RECORD:' ) ;
2079 oe_debug_pub.add( ' orig_sys_document_ref: ' || p_old_line_tbl(j).orig_sys_document_ref) ;
2080 oe_debug_pub.add( ' line_id : ' || p_old_line_tbl(j).line_id) ;
2081 oe_debug_pub.add( ' flow_status_code : ' || p_old_line_tbl(j).flow_status_code) ;
2082 oe_debug_pub.add( ' request_id : ' || p_old_line_tbl(j).request_id) ;
2083 oe_debug_pub.add( ' operation : ' || p_old_line_tbl(j).operation) ;
2084 oe_debug_pub.add( ' unit_selling_price : ' || p_old_line_tbl(j).unit_selling_price) ;
2085 oe_debug_pub.add( ' ordered_quantity : ' || p_old_line_tbl(j).ordered_quantity) ;
2086 oe_debug_pub.add( ' order_quantity_uom : ' || p_old_line_tbl(j).order_quantity_uom) ;
2087 oe_debug_pub.add( ' schedule_arrival_date: ' || p_old_line_tbl(j).schedule_arrival_date) ;
2088 oe_debug_pub.add( ' shipped_quantity : ' || p_old_line_tbl(j).shipped_quantity) ;
2089 oe_debug_pub.add( ' schedule_ship_date : ' || p_old_line_tbl(j).schedule_ship_date) ;
2090 oe_debug_pub.add( ' transaction type : ' || p_old_line_tbl(j).xml_transaction_type_code) ;
2091 oe_debug_pub.add( 'NEW LINE RECORD:' ) ;
2092 oe_debug_pub.add( ' orig_sys_document_ref: ' || l_line_tbl(i).orig_sys_document_ref) ;
2093 oe_debug_pub.add( ' line_id : ' || l_line_tbl(i).line_id) ;
2094 oe_debug_pub.add( ' flow_status_code : ' || l_line_tbl(i).flow_status_code) ;
2095 oe_debug_pub.add( ' request_id : ' || l_line_tbl(i).request_id) ;
2096 oe_debug_pub.add( ' operation : ' || l_line_tbl(i).operation) ;
2097 oe_debug_pub.add( ' unit_selling_price : ' || l_line_tbl(i).unit_selling_price) ;
2098 oe_debug_pub.add( ' ordered_quantity : ' || l_line_tbl(i).ordered_quantity) ;
2099 oe_debug_pub.add( ' order_quantity_uom : ' || l_line_tbl(i).order_quantity_uom) ;
2100 oe_debug_pub.add( ' schedule_arrival_date: ' || l_line_tbl(i).schedule_arrival_date) ;
2101 oe_debug_pub.add( ' shipped_quantity : ' || l_line_tbl(i).shipped_quantity) ;
2102 oe_debug_pub.add( ' schedule_ship_date : ' || l_line_tbl(i).schedule_ship_date) ;
2103 oe_debug_pub.add( ' transaction type : ' || l_line_tbl(i).xml_transaction_type_code) ;
2104 END IF;
2105
2106 -- { start j <> 0
2107 if j <> 0 then
2108
2109 if nvl(l_line_tbl(i).unit_selling_price, 0) <>
2110 nvl(p_old_line_tbl(j).unit_selling_price, 0) then
2111 -- set flag to indicate that unit_selling_price has changed
2112 IF l_debug_level > 0 THEN
2113 oe_debug_pub.add( 'SET FLAG TO INDICATE THAT UNIT_SELLING_PRICE' ) ;
2114 END IF;
2115 l_change_usp := 'Y';
2116 l_change_usp_cso := 'Y';
2117 end if; -- unit_selling_price
2118
2119 if nvl(l_line_tbl(i).ordered_quantity, 0) <>
2120 nvl(p_old_line_tbl(j).ordered_quantity, 0) then
2121 -- set flag to indicate that ordered_quantity has changed
2122 IF l_debug_level > 0 THEN
2123 oe_debug_pub.add( 'SET FLAG TO INDICATE THAT ORDERED_QUANTITY' ) ;
2124 END IF;
2125 l_change_oqty := 'Y';
2126 l_change_oqty_cso := 'Y';
2127 -- GENESIS --
2128 l_sync_line := 'Y';
2129 l_insert_sync_line := 'Y';
2130 l_change_type := 'SPLIT';
2131 IF l_debug_level > 0 THEN
2132 oe_debug_pub.add( 'Genesis: Set line flag for ordered quantity change');
2133 END IF;
2134 -- GENESIS --
2135 end if; -- ordered_quantity
2136
2137 if not oe_globals.equal (l_line_tbl(i).order_quantity_uom,
2138 p_old_line_tbl(j).order_quantity_uom) then
2139 -- set flag to indicate that uom has changed
2140 IF l_debug_level > 0 THEN
2141 oe_debug_pub.add( 'SET FLAG TO INDICATE THAT ORDER_QUANTITY_UOM' ) ;
2142 END IF;
2143 l_change_uom := 'Y';
2144 l_change_uom_cso := 'Y';
2145 end if;
2146
2147 if nvl(l_line_tbl(i).schedule_arrival_date, trunc(sysdate)) <>
2148 nvl(p_old_line_tbl(j).schedule_arrival_date, trunc(sysdate)) then
2149 -- set flag to indicate that schedule_arrival_date has changed
2150 IF l_debug_level > 0 THEN
2151 oe_debug_pub.add( 'SET FLAG TO INDICATE THAT SCHEDULE_ARRIVAL_DATE' ) ;
2152 END IF;
2153 l_change_sadt := 'Y';
2154 -- GENESIS --
2155 l_sync_line := 'Y';
2156 l_insert_sync_line := 'Y';
2157 l_change_type := 'ARVL_DT_CHG';
2158 IF l_debug_level > 0 THEN
2159 oe_debug_pub.add( 'Genesis: Set line flag for schedule arrival date change');
2160 END IF;
2161 -- GENESIS --
2162 end if; -- schedule_arrival_date
2163 if nvl(l_line_tbl(i).shipped_quantity, 0) <>
2164 nvl(p_old_line_tbl(j).shipped_quantity, 0) then
2165 -- set flag to indicate that shipped_quantity has changed
2166 IF l_debug_level > 0 THEN
2167 oe_debug_pub.add( 'SET FLAG TO INDICATE THAT SHIPPED_QUANTITY' ) ;
2168 END IF;
2169 l_change_sqty := 'Y';
2170 end if; -- shipped_quantity
2171 if nvl(l_line_tbl(i).schedule_ship_date, trunc(sysdate)) <>
2172 nvl(p_old_line_tbl(j).schedule_ship_date, trunc(sysdate)) then
2173 -- set flag to indicate that schedule_ship_date has changed
2174 IF l_debug_level > 0 THEN
2175 oe_debug_pub.add( 'SET FLAG TO INDICATE THAT SCHEDULE_SHIP_DATE' ) ;
2176 END IF;
2177 l_change_ssdt := 'Y';
2178 l_change_ssdt_cso := 'Y';
2179 -- GENESIS --
2180 l_sync_line := 'Y';
2181 l_insert_sync_line := 'Y';
2182 l_change_type := 'SHIP_DT_CHG';
2183 IF l_debug_level > 0 THEN
2184 oe_debug_pub.add( 'Genesis: Set line flag for schedule ship date change');
2185 END IF;
2186 -- GENESIS --
2187 end if; -- schedule_ship_date
2188
2189 -- O2C 25: ship from org id (Bug 8722247)
2190 IF Nvl(l_line_tbl(i).ship_from_org_id, -99) <>
2191 Nvl(p_old_line_tbl(j).ship_from_org_id, -99) THEN
2192
2193 l_sync_line := 'Y';
2194 l_insert_sync_line := 'Y';
2195 l_change_type := 'SHP_FRM_CHG';
2196 IF l_debug_level > 0 THEN
2197 oe_debug_pub.ADD('Genesis: set line flag for ship from org id change');
2198 END IF;
2199 END IF;
2200 -- O2C 25:ship from org id
2201
2202 -- O2C 25: shipping_method_code (Bug 8936919 gabhatia)
2203 IF Nvl(l_line_tbl(i).shipping_method_code, -99) <>
2204 Nvl(p_old_line_tbl(j).shipping_method_code, -99) THEN
2205
2206 l_sync_line := 'Y';
2207 l_insert_sync_line := 'Y';
2208 l_change_type := 'SHP_MTH_CHG';
2209 IF l_debug_level > 0 THEN
2210 oe_debug_pub.ADD('Genesis: set line flag for shipping_method_code change');
2211 END IF;
2212 END IF;
2213 -- O2C 25:shipping_method_code (End changes for Bug 8936919 gabhatia)
2214
2215
2216 -- GENESIS --
2217 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) AND
2218 -- O2C25: 8516700: Start: Commented special processing for BOOKED,
2219 -- and remove hardcoding on flow status codes.
2220 -- l_line_tbl(i).flow_status_code <> 'BOOKED' AND
2221 -- l_line_tbl(i).flow_status_code in ('FULFILLED','AWAITING_SHIPPING','SHIPPED','CLOSED','SUPPLY_ELIGIBLE') AND
2222 Oe_Genesis_Util.Status_Needs_Sync(l_line_tbl(i).flow_status_code) AND
2223 -- O2C25: 8516700: End
2224 l_line_tbl(i).flow_status_code <> p_old_line_tbl(j).flow_status_code THEN
2225
2226 l_change_fsc := 'Y';
2227 l_sync_line := 'Y';
2228 l_insert_sync_line := 'Y';
2229 l_change_type := 'LINE_STATUS';
2230
2231 IF l_debug_level > 0 THEN
2232 oe_debug_pub.add( 'Genesis: Set line flag for line flow status code change');
2233 END IF;
2234 END IF;
2235
2236 -- Call Sync Line to process multiple lines ZB
2237 IF l_debug_level > 0 THEN
2238 oe_debug_pub.add( 'Genesis: l_sync_line '|| l_sync_line);
2239 oe_debug_pub.add( 'Genesis: l_insert_sync_line '|| l_insert_sync_line);
2240 oe_debug_pub.add( 'Genesis: order_source_id '|| l_header_rec.order_source_id);
2241 END IF;
2242
2243 IF l_insert_sync_line = 'Y' and
2244 (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
2245 -- Bug# 12879272
2246 -- Checking if the Acknowledgement is already inserted
2247 -- When a line is created through Process Order API, and Booking and Scheduling of line
2248 -- happens in the same call, then Acknowledgement is inserted TWICE, to avoid that
2249 -- we are checking the flag l_sync_line_inserted
2250 IF NOT l_sync_line_inserted THEN
2251 OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(i),
2252 p_change_type => l_change_type,
2253 p_req_id => l_itemkey_sso,
2254 X_RETURN_STATUS => L_RETURN_STATUS);
2255 l_sync_line_inserted := TRUE;
2256 oe_debug_pub.add('l_sync_line_inserted: TRUE');
2257 END IF;
2258 -- end of IF added for Bug# 12879272
2259 END IF;
2260 l_insert_sync_line := 'N';
2261 IF l_debug_level > 0 THEN
2262 oe_debug_pub.add( 'Genesis: after insert :l_insert_sync_line '|| l_insert_sync_line);
2263 END IF;
2264 -- GENESIS --
2265
2266 IF l_change_oqty_cso = 'Y' OR
2267 l_change_uom_cso = 'Y' OR
2268 l_change_usp_cso = 'Y' OR
2269 (l_change_ssdt_cso = 'Y' AND
2270 nvl(p_old_line_tbl(j).schedule_ship_date, FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE)
2271 THEN
2272 l_apply_3a7_hold := TRUE;
2273 END IF;
2274 end if; -- j <> 0
2275 -- end j <> 0 }
2276 end If; -- Update operation
2277 -- End for the comparison when the operation on the Line table is UPDATE }
2278
2279 -- { Start of processing to see if the insert is required if YES
2280 -- Then what should the status be.
2281
2282 -- We should insert into Line table for the following conditions
2283 -- a. It the operation is insert, this means the new line is added
2284 -- to a BOOKED order.
2285 -- b. If j = 0, means this line is creating during process order
2286 -- call, and might be a new line so send the data in 3A6 (we no longer do this)
2287
2288 -- c. Any of the flag is set to 'Y', means change to the triggering
2289 -- columns has been made send the 3A6
2290
2291 -- d. set the flag to insert the header too, if flag at header level
2292 -- is not set yet.
2293
2294 -- { Start of a., b., c. and d.
2295
2296 -- changed for bug 3424468 to check operation code on old line instead of new line
2297
2298 ---
2299 --
2300 -- Bug 10407362
2301 --
2302 -- In case 'j' is zero, there would be a NO_DATA_FOUND exception.
2303 -- So, we call this API if and only if 'j' is not equal to 0.
2304 ---
2305 IF j <> 0 THEN
2306 If l_debug_level > 0 Then
2307 Oe_Debug_Pub.Add('OEXPACKB: - value of j: ' || j);
2308 End If;
2309 is_line_exists(p_line_tbl(j).line_id,l_line_exists); -- Bug 9685021
2310 If l_debug_level > 0 Then
2311 Oe_Debug_Pub.Add('OEXPACKB: - Loc 1 Done with is_line_exists; return value: ' || l_line_exists);
2312 End If;
2313 END IF;
2314
2315
2316 if j <> 0 AND -- Bug 10407362: Same Check for 'j' as mentioned above.
2317 ( p_old_line_tbl(j).operation = Oe_Globals.G_OPR_INSERT or
2318 p_old_line_tbl(j).operation = Oe_Globals.G_OPR_CREATE or
2319 (l_line_exists ='N' and p_line_tbl(j).booked_flag ='Y' -- Added Condition for bug 9685021
2320 and NOT OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id) ) -- bug 11078158
2321 ) then
2322
2323 IF l_debug_level > 0 THEN
2324 oe_debug_pub.add( 'OEXPACKB: LINE OPERATIONS IS INSERT' ) ;
2325 oe_debug_pub.add( 'OEXPACKB: - p_old_line_id(j)'||p_old_line_tbl(j).line_id) ;
2326 oe_debug_pub.add( 'OEXPACKB: - p_old_line_id(i)'||p_old_line_tbl(i).line_id) ;
2327 oe_debug_pub.add( 'OEXPACKB: - p_line_id'||p_line_tbl(i).line_id ) ;
2328 END IF;
2329 ------------------------------
2330 -- GENESIS --
2331 -- Added this code to handle split case
2332 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
2333 IF l_debug_level > 0 THEN
2334 oe_debug_pub.add( 'OEXPACKB: - p_line_id(i).item_type_code'||p_line_tbl(i).item_type_code) ;
2335 oe_debug_pub.add( 'OEXPACKB: - p_line_id(i).split_from_line_id'||p_line_tbl(i).split_from_line_id) ;
2336 END IF;
2337 l_sync_line := 'Y';
2338 IF (l_line_tbl(i).SPLIT_FROM_LINE_ID IS NOT NULL OR l_line_tbl(i).item_type_code <> 'CONFIG') THEN
2339 -- Bug# 12879272
2340 -- Checking if the Acknowledgement is already inserted during the UPDATE operation check above.
2341 -- When a line is created through Process Order API, and Booking and Scheduling of line
2342 -- happens in the same call, then Acknowledgement is inserted TWICE, to avoid this
2343 -- we are checking the flag l_sync_line_inserted
2344 IF NOT l_sync_line_inserted THEN
2345 OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(i),
2346 p_change_type => l_change_type,
2347 p_req_id => l_itemkey_sso,
2348 X_RETURN_STATUS => L_RETURN_STATUS);
2349 l_sync_line_inserted := TRUE;
2350 oe_debug_pub.add('l_sync_line_inserted: TRUE');
2351 ELSE
2352 oe_debug_pub.add('No need to insert an acknowledgement, since one was already inserted');
2353 END IF;
2354 -- end of IF added for Bug# 12879272
2355 END IF;
2356 END IF;
2357 -- GENESIS --
2358 ------------------------------
2359 l_line_req := 'Y';
2360 l_line_status := 'OPEN';
2361
2362 -- trigger 3A7 for new line added to booked order
2363 -- and apply hold, unless line was added by buyer
2364 if nvl(OE_ORDER_UTIL.g_header_rec.xml_transaction_type_code, G_TRANSACTION_CSO)
2365 = G_TRANSACTION_CSO THEN
2366 l_apply_3a7_hold := TRUE;
2367 l_line_req_cso := 'Y';
2368 end if;
2369 l_line_status_cso := 'OPEN';
2370 ------------------------------
2371 elsif l_change_usp = 'Y' then
2372 ------------------------------
2373 l_line_req := 'Y';
2374 l_line_status := 'OPEN';
2375 ------------------------------
2376 elsif (l_change_oqty = 'Y' or l_change_uom = 'Y') then
2377 ------------------------------
2378 l_line_req := 'Y';
2379 if l_line_tbl(i).ordered_quantity = 0 then
2380 l_line_status := 'CANCELLED';
2381 else
2382 l_line_status := 'OPEN';
2383 end if;
2384 ------------------------------
2385 elsif l_change_sadt = 'Y' then
2386 ------------------------------
2387 l_line_req := 'Y';
2388 if l_line_tbl(i).flow_status_code = 'CANCELLED' then
2389 l_line_status := 'CANCELLED';
2390 else
2391 l_line_status := 'OPEN';
2392 end if;
2393 ------------------------------
2394 elsif l_change_ssdt = 'Y' then
2395 ------------------------------
2396 l_line_req := 'Y';
2397 l_line_status := 'OPEN';
2398 ------------------------------
2399 elsif l_change_sqty = 'Y' then
2400 ------------------------------
2401 l_line_req := 'Y';
2402 l_line_status := 'SHIPPED';
2403 ------------------------------
2404 -- GENESIS --
2405 elsif l_change_fsc = 'Y' then
2406 ------------------------------
2407 l_line_req := 'Y';
2408 l_line_status := l_line_tbl(i).flow_status_code;
2409 ------------------------------
2410 -- GENESIS --
2411 end if;
2412
2413 --------------------------------
2414 -- 3A7 changes, arihan
2415 --------------------------------
2416 if l_change_usp_cso = 'Y' then
2417 ------------------------------
2418 l_line_req_cso := 'Y';
2419 l_line_status_cso := 'OPEN';
2420 ------------------------------
2421 elsif (l_change_oqty_cso = 'Y' or l_change_uom_cso = 'Y') then
2422 ------------------------------
2423 l_line_req_cso := 'Y';
2424 if l_line_tbl(i).ordered_quantity = 0 then
2425 l_line_status_cso := 'CANCELLED';
2426 else
2427 l_line_status_cso := 'OPEN';
2428 end if;
2429 ------------------------------
2430 elsif l_change_ssdt_cso = 'Y' then
2431 ------------------------------
2432 l_line_req_cso := 'Y';
2433 l_line_status_cso := 'OPEN';
2434 ------------------------------
2435 end if;
2436
2437 -- checking for closed line status. The only case we would ignore is when a line
2438 -- was just shipped or cancelled. In these cases we would allow that to be
2439 -- the line status, as they reflect the reason for the 3a6. Otherwise if the line
2440 -- is closed, we should reflect that
2441
2442 if ((l_line_status <> 'SHIPPED') And
2443 (l_line_status <> 'CANCELLED') And
2444 (l_line_tbl(i).flow_status_code = 'CLOSED')) Then
2445
2446 l_line_status := 'CLOSED';
2447 l_line_status_cso := 'CLOSED';
2448 end if;
2449 -- End of a., b. and c. }
2450
2451 if l_line_req = 'Y' and
2452 l_header_req <> 'Y' and l_header_req <> 'D' then
2453 l_header_req := 'Y';
2454 l_header_status := 'OPEN';
2455 end if;
2456
2457 -- GENESIS --
2458 IF l_sync_line = 'Y' THEN
2459 l_sync_header := 'Y';
2460 END IF;
2461 -- GENESIS --
2462
2463 -- End of processing to see if the insert is required }
2464
2465
2466 -- the following condition tests if a line was sent by the buyer or not
2467 -- it compares the header request id to the line request id since
2468 -- Order Import will always populate these with the same value for
2469 -- header and line
2470 -- an exception is made for header-level cancellations
2471 IF l_is_delivery_required_cso = 'Y' THEN
2472 IF (OE_ORDER_UTIL.G_HEADER_REC.xml_transaction_type_code = G_TRANSACTION_CHO
2473 AND OE_GLOBALS.Equal (l_header_rec.request_id, l_line_tbl(i).request_id)
2474 AND nvl(l_header_rec.request_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) OR
2475
2476 (OE_ORDER_UTIL.G_HEADER_REC.xml_transaction_type_code = G_TRANSACTION_CPO
2477 AND OE_GLOBALS.Equal (l_header_rec.request_id, l_line_tbl(i).request_id)
2478 AND nvl(l_header_rec.request_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) OR
2479
2480 (OE_ORDER_UTIL.G_HEADER_REC.xml_transaction_type_code = G_TRANSACTION_CPO
2481 AND nvl(l_header_rec.cancelled_flag,'N') = 'Y')
2482 THEN
2483 IF l_debug_level > 0 THEN
2484 oe_debug_pub.add('3A7 Buyer change detected, line will not be sent or put on hold');
2485 END IF;
2486 l_3a7_buyer_line := TRUE;
2487 l_line_req_cso := 'N'; -- don't insert the line
2488 END IF;
2489
2490 END IF;
2491
2492 if l_line_req_cso = 'Y' and
2493 l_header_req_cso <> 'Y' and l_header_req_cso <> 'D' then
2494 l_header_req_cso := 'Y';
2495 l_header_status_cso := 'OPEN';
2496 end if;
2497
2498 -- start of checking to see if 3A7 hold needs to be applied
2499 -- Note that we only apply a hold if line was not sent in by the buyer
2500 IF l_apply_3a7_hold THEN
2501 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
2502 IF l_is_delivery_required_cso = 'Y'
2503 AND l_cso_response_profile = 'Y'
2504 AND NOT l_3a7_buyer_line
2505 AND nvl(OE_GLOBALS.G_XML_TXN_CODE, G_TRANSACTION_CSO) <> G_TRANSACTION_POI
2506 AND l_line_tbl(i).order_source_id = G_XML_ORDER_SOURCE_ID
2507 AND l_line_tbl(i).ordered_quantity <> 0
2508 AND nvl(l_line_tbl(i).booked_flag, 'N') = 'Y'
2509 AND nvl(l_line_tbl(i).xml_transaction_type_code, G_TRANSACTION_CSO) = G_TRANSACTION_CSO
2510 THEN
2511
2512 IF l_debug_level > 0 THEN
2513 oe_debug_pub.add( 'Calling OE_Acknowlegment_PUB.Apply_3A7_Hold', 2 ) ;
2514 END IF;
2515 OE_Acknowledgment_PUB.Apply_3A7_Hold
2516 ( p_header_id => l_line_tbl(i).header_id
2517 , p_line_id => l_line_tbl(i).line_id
2518 , p_sold_to_org_id => l_line_tbl(i).sold_to_org_id
2519 , p_tp_check => FND_API.G_FALSE
2520 , x_return_status => l_return_status);
2521 IF l_debug_level > 0 THEN
2522 oe_debug_pub.add( 'Return status after call to apply_3a7_hold:' || l_return_status, 2 ) ;
2523 END IF;
2524 END IF;
2525 END IF;
2526 END IF;
2527 -- end of 3a7 hold application
2528
2529 -- { Start of l_line_req
2530 IF l_debug_level > 0 THEN
2531 oe_debug_pub.add( 'START 3A6 PROCESSING ON HEADER/LINES' ) ;
2532 oe_debug_pub.add( ' L_HEADER_STATUS : ' || L_HEADER_STATUS ) ;
2533 oe_debug_pub.add( ' L_HEADER_REQ : ' || L_HEADER_REQ ) ;
2534 oe_debug_pub.add( ' L_LINE_REQ : ' || L_LINE_REQ ) ;
2535 END IF;
2536 if (l_line_req = 'Y') OR (l_header_status = 'BOOKED') then
2537 -- Call insert routine to insert header_id and status info
2538 If l_header_req = 'Y' Then
2539
2540 If l_header_rec.flow_status_code = 'CANCELLED' then
2541 l_header_status := 'CANCELLED';
2542 Elsif l_header_rec.flow_status_code = 'CLOSED' then
2543 l_header_status := 'CLOSED';
2544 End If;
2545
2546 -- GENESIS --
2547 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
2548 l_ack_type := G_TRANSACTION_SEBL;
2549 ELSE
2550 l_ack_type := G_TRANSACTION_SSO;
2551 END IF;
2552 -- GENESIS --
2553
2554 If l_is_delivery_required = 'Y' Then
2555 Insert_Header ( p_header_rec => l_header_rec,
2556 p_header_status => l_header_status,
2557 p_ack_type => l_ack_type, -- GENESIS G_TRANSACTION_SSO,
2558 p_itemkey => l_itemkey_sso,
2559 x_return_status => l_return_status
2560 );
2561 End If;
2562 If l_return_status = FND_API.G_RET_STS_SUCCESS Then
2563 l_header_req := 'D';
2564 End If;
2565 End If;
2566 -- Call insert routine to insert line_id and status info
2567 -- which will later be used to populate data in line ack
2568 -- table
2569 IF l_debug_level > 0 THEN
2570 oe_debug_pub.add( 'INSERTING LINE RECORD WITH INDEX = ' || I ) ;
2571 END IF;
2572 l_line_rec := l_line_tbl(i);
2573
2574 --ensure that a valid line has been pulled from the lines table
2575 If l_line_rec.line_id is not null then
2576 IF l_debug_level > 0 THEN
2577 oe_debug_pub.add( ' L_HEADER_STATUS : ' || L_HEADER_STATUS ) ;
2578 oe_debug_pub.add( ' L_HEADER_REQ : ' || L_HEADER_REQ ) ;
2579 oe_debug_pub.add( ' L_LINE_REQ : ' || L_LINE_REQ ) ;
2580 oe_debug_pub.add( ' L_LINE_STATUS : ' || L_LINE_STATUS ) ;
2581 END IF;
2582
2583 -- GENESIS --
2584 oe_debug_pub.add( 'Genesis: 6.1');
2585 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
2586 l_ack_type := G_TRANSACTION_SEBL;
2587 ELSE
2588 l_ack_type := G_TRANSACTION_SSO;
2589 END IF;
2590 -- GENESIS --
2591 If l_is_delivery_required = 'Y' Then
2592 Insert_Line ( p_line_rec => l_line_rec,
2593 p_line_status => l_line_status,
2594 p_ack_type => l_ack_type, -- GENESIS G_TRANSACTION_SSO,
2595 p_itemkey => l_itemkey_sso,
2596 x_return_status => l_return_status
2597 );
2598 End If;
2599 x_return_status := l_return_status;
2600 End If;
2601 end if;
2602
2603 -- { Start of l_line_req_cso
2604 IF l_debug_level > 0 THEN
2605 oe_debug_pub.add( 'START 3A7 PROCESSING ON HEADER/LINES' ) ;
2606 oe_debug_pub.add( ' L_HEADER_STATUS IS : ' || L_HEADER_STATUS_CSO ) ;
2607 oe_debug_pub.add( ' L_HEADER_REQ_CSO : ' || L_HEADER_REQ_CSO ) ;
2608 oe_debug_pub.add( ' L_LINE_REQ_CSO : ' || L_LINE_REQ_CSO ) ;
2609 END IF;
2610 if (l_line_req_cso = 'Y') OR (l_header_status_cso = 'BOOKED') then
2611 -- Call insert routine to insert header_id and status info
2612 If l_header_req_cso = 'Y' Then
2613
2614 If l_header_rec.flow_status_code = 'CANCELLED' then
2615 l_header_status_cso := 'CANCELLED';
2616 Elsif l_header_rec.flow_status_code = 'CLOSED' then
2617 l_header_status_cso := 'CLOSED';
2618 End If;
2619 If l_is_delivery_required_cso = 'Y' Then
2620 -- If l_order_booked_cso = 'Y' Then
2621 Insert_Header ( p_header_rec => l_header_rec,
2622 p_header_status => l_header_status_cso,
2623 p_ack_type => G_TRANSACTION_CSO,
2624 p_itemkey => l_itemkey_cso,
2625 x_return_status => l_return_status
2626 );
2627 -- End If;
2628 End If;
2629 If l_return_status = FND_API.G_RET_STS_SUCCESS Then
2630 l_header_req_cso := 'D';
2631 End If;
2632 End If;
2633 -- Call insert routine to insert line_id and status info
2634 -- which will later be used to populate data in line ack
2635 -- table
2636 IF l_debug_level > 0 THEN
2637 oe_debug_pub.add( 'INSERTING LINE RECORD WITH INDEX = ' || I ) ;
2638 END IF;
2639 l_line_rec := l_line_tbl(i);
2640
2641 --ensure that a valid line has been pulled from the lines table
2642 If l_line_rec.line_id is not null then
2643 IF l_debug_level > 0 THEN
2644 oe_debug_pub.add( ' L_HEADER_STATUS IS : ' || L_HEADER_STATUS_CSO ) ;
2645 oe_debug_pub.add( ' L_HEADER_REQ_CSO : ' || L_HEADER_REQ_CSO ) ;
2646 oe_debug_pub.add( ' L_LINE_REQ_CSO : ' || L_LINE_REQ_CSO ) ;
2647 oe_debug_pub.add( ' L_LINE_STATUS : ' || L_LINE_STATUS_CSO ) ;
2648 END IF;
2649 If l_is_delivery_required_cso = 'Y' Then
2650 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510'
2651 AND l_cso_response_profile = 'Y' THEN
2652 -- Check if Hold already exists on this order line
2653 IF l_debug_level > 0 THEN
2654 oe_debug_pub.add( 'Process SSO: Check If Hold Already Applied' ) ;
2655 END IF;
2656
2657 OE_HOLDS_PUB.Check_Holds
2658 ( p_api_version => 1.0
2659 , p_header_id => l_header_rec.header_id
2660 , p_line_id => l_line_tbl(i).line_id
2661 , p_hold_id => l_hold_id
2662 , p_entity_code => 'O'
2663 , p_entity_id => l_header_rec.header_id
2664 , x_result_out => l_hold_result
2665 , x_msg_count => l_msg_count
2666 , x_msg_data => l_msg_data
2667 , x_return_status => l_return_status
2668 );
2669
2670 IF l_hold_result = FND_API.G_TRUE THEN
2671 IF l_debug_level > 0 THEN
2672 oe_debug_pub.add( 'Process SSO: Hold Already Applied On Header Id:'
2673 ||l_header_rec.header_id ||': Line_Id:'||l_line_tbl(i).line_id) ;
2674 oe_debug_pub.add( 'Change line status for 3A7');
2675 END IF;
2676 l_line_status_cso := 'ON HOLD';
2677 END IF ;
2678 END IF;
2679
2680 Insert_Line ( p_line_rec => l_line_rec,
2681 p_line_status => l_line_status_cso,
2682 p_ack_type => G_TRANSACTION_CSO,
2683 p_itemkey => l_itemkey_cso,
2684 x_return_status => l_return_status
2685 );
2686 End If;
2687 x_return_status := l_return_status;
2688 End If;
2689 end if;
2690 -- End of l_line_req_cso }
2691
2692 i := l_line_tbl.Next(i);
2693 end loop;
2694 -- End step 2b. }
2695 -- End step 2. }
2696
2697 -- { Start of 3.
2698 -- Check if the Header Insert is required
2699
2700 if l_header_req = 'Y' Then
2701
2702 If l_header_rec.flow_status_code = 'CANCELLED' then
2703 l_header_status := 'CANCELLED';
2704 Elsif l_header_rec.flow_status_code = 'CLOSED' then
2705 l_header_status := 'CLOSED';
2706 End If;
2707
2708 -- GENESIS --
2709 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
2710 l_ack_type := G_TRANSACTION_SEBL;
2711 ELSE
2712 l_ack_type := G_TRANSACTION_SSO;
2713 END IF;
2714 -- GENESIS --
2715
2716 If l_is_delivery_required = 'Y' Then
2717 Insert_Header ( p_header_rec => l_header_rec,
2718 p_header_status => l_header_status,
2719 p_ack_type => l_ack_type, -- GENESIS G_TRANSACTION_SSO,
2720 p_itemkey => l_itemkey_sso,
2721 x_return_status => l_return_status
2722 );
2723 End If;
2724 if l_return_status = FND_API.G_RET_STS_SUCCESS Then
2725 l_header_req := 'D';
2726 end if;
2727 end if;
2728 if l_header_req_cso = 'Y' Then
2729
2730 If l_header_rec.flow_status_code = 'CANCELLED' then
2731 l_header_status_cso := 'CANCELLED';
2732 Elsif l_header_rec.flow_status_code = 'CLOSED' then
2733 l_header_status_cso := 'CLOSED';
2734 End If;
2735 If l_is_delivery_required_cso = 'Y' Then
2736 -- If l_order_booked_cso = 'Y' Then -- for beta release we only want to send 3a7 on booking
2737 Insert_Header ( p_header_rec => l_header_rec,
2738 p_header_status => l_header_status_cso,
2739 p_ack_type => G_TRANSACTION_CSO,
2740 p_itemkey => l_itemkey_cso,
2741 x_return_status => l_return_status
2742 );
2743 -- End If;
2744 End If;
2745 if l_return_status = FND_API.G_RET_STS_SUCCESS Then
2746 l_header_req_cso := 'D';
2747 end if;
2748 end if;
2749 if l_header_req = 'D' then
2750 -- Raise ShowSo Event
2751 If l_is_delivery_required = 'Y' Then
2752
2753 IF l_debug_level > 0 THEN
2754 oe_debug_pub.add( 'Raising 3a6 event with itemkey: ' || l_itemkey_sso) ;
2755 END IF;
2756
2757 OE_Acknowledgment_Pub.Raise_Event_Showso
2758 (p_header_id => l_header_rec.header_id,
2759 p_line_id => Null,
2760 p_customer_id => l_header_rec.sold_to_org_id,
2761 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
2762 p_change_sequence => l_header_rec.change_sequence,
2763 p_itemtype => Null,
2764 p_itemkey => l_itemkey_sso,
2765 p_party_id => l_party_id,
2766 p_party_site_id => l_party_site_id,
2767 p_transaction_type => G_TRANSACTION_SSO,
2768 p_org_id => l_header_rec.org_id, /* Bug 5472200 */
2769 x_return_status => l_return_status);
2770
2771 End If;
2772 end if;
2773 if l_header_req_cso = 'D' then
2774 If l_is_delivery_required_cso = 'Y' Then
2775 -- If l_order_booked_cso = 'Y' Then
2776 IF l_debug_level > 0 THEN
2777 oe_debug_pub.add( 'Raising 3a7 event with itemkey: ' || l_itemkey_cso) ;
2778 END IF;
2779
2780 OE_Acknowledgment_Pub.Raise_Event_Showso
2781 (p_header_id => l_header_rec.header_id,
2782 p_line_id => Null,
2783 p_customer_id => l_header_rec.sold_to_org_id,
2784 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
2785 p_change_sequence => l_header_rec.change_sequence,
2786 p_itemtype => Null,
2787 p_itemkey => l_itemkey_cso,
2788 p_party_id => l_party_id,
2789 p_party_site_id => l_party_site_id,
2790 p_transaction_type => G_TRANSACTION_CSO,
2791 p_org_id => l_header_rec.org_id, /* Bug 5472200 */
2792 x_return_status => l_return_status);
2793 -- End If;
2794 End If;
2795 end if;
2796
2797 -- GENESIS --
2798 l_sync_header_id := NULL;
2799 l_hdr_req_id := NULL;
2800 l_sync_line_id := NULL;
2801 l_lin_req_id := NULL;
2802
2803 IF l_debug_level > 0 THEN
2804 oe_debug_pub.add('GENESIS PROCESS_SSO : l_sync_header:'|| l_sync_header || ', l_sync_line:'
2805 || l_sync_line || ', l_change_type:'
2806 || l_change_type);
2807 END IF;
2808
2809 IF ( (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) AND l_sync_header = 'Y') THEN
2810
2811 IF l_debug_level > 0 THEN
2812 oe_debug_pub.add('TESTING GENESIS 15...'||l_header_rec.order_source_id);
2813 END IF;
2814
2815 IF l_sync_header = 'Y' THEN
2816 l_sync_header_id := l_header_rec.header_id;
2817 l_hdr_req_id := l_header_rec.request_id;
2818 END IF;
2819
2820 IF l_sync_line = 'Y' THEN
2821 l_sync_line_id := l_line_rec.line_id;
2822 l_lin_req_id := l_line_rec.request_id;
2823 END IF;
2824
2825 IF l_debug_level > 0 THEN
2826 oe_debug_pub.add('TESTING GENESIS 16...l_header_rec.request_id'||l_header_rec.request_id);
2827 oe_debug_pub.add('TESTING GENESIS 16...l_line_rec.request_id'||l_line_rec.request_id);
2828 oe_debug_pub.add('TESTING GENESIS 16...l_hdr_req_id'||l_hdr_req_id);
2829 oe_debug_pub.add('TESTING GENESIS 16...l_lin_req_id'|| l_lin_req_id);
2830 oe_debug_pub.add('TESTING GENESIS 16...l_line_rec.flow_status_code'||l_line_rec.flow_status_code );
2831 END IF;
2832
2833 OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => l_header_rec
2834 ,p_line_rec => null
2835 ,p_hdr_req_id => l_itemkey_sso
2836 ,p_lin_req_id => l_itemkey_sso
2837 ,p_change_type => Nvl(l_change_type, 'OTHERS') ); -- Bug 13008311
2838
2839 l_sync_header := 'N';
2840 l_sync_line := 'N';
2841 END IF;
2842 -- GENESIS --
2843
2844 x_return_status := l_return_status;
2845
2846 -- End of 3. }
2847
2848 IF l_debug_level > 0 THEN
2849 oe_debug_pub.add( 'OEXPACKB: EXITING PROCESS_SSO' ) ;
2850 END IF;
2851 Exception
2852 When Others Then
2853 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2854 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'process_sso');
2855 End if;
2856 END Process_SSO;
2857 -- End of the Process_SSO }
2858
2859 -- { Start of the Process_SSO_Conc_Pgm
2860 -- This api will do following
2861 -- 1. Get the Orders for the Customer (TP), range of orders,
2862 -- range of order dates, range of customer po and order status
2863 -- Open right now, later we might have to support Close)
2864 -- 2. For each order for the requested criteria raise the event to run the
2865 -- the Show Sales Order WF, which will genrate the SSO document
2866 PROCEDURE Process_SSO_CONC_PGM
2867 ( errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR,
2868 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
2869 p_operating_unit IN NUMBER,
2870 p_customer_id IN NUMBER,
2871 p_open_orders_only IN VARCHAR2,
2872 p_closed_for_days IN NUMBER,
2873 p_so_number_from IN NUMBER,
2874 p_so_number_to IN NUMBER,
2875 p_so_date_from IN VARCHAR2,
2876 p_so_date_to IN VARCHAR2,
2877 p_customer_po_no_from IN VARCHAR2,
2878 p_customer_po_no_to IN VARCHAR2
2879 )
2880 IS
2881
2882 l_msg_count NUMBER := 0 ;
2883 l_msg_data VARCHAR2(2000):= NULL ;
2884 l_message_text VARCHAR2(2000);
2885
2886 --l_debug_level NUMBER := to_number(nvl(fnd_profile.value('ONT_DEBUG_LEVEL'),'0'));
2887 l_filename VARCHAR2(200);
2888 l_request_id NUMBER;
2889 l_org_id NUMBER;
2890 l_order_rec Order_Rec_Type;
2891 l_order_count NUMBER;
2892 l_return_status VARCHAR2(30);
2893 l_so_date_from DATE;
2894 l_so_date_to DATE;
2895 l_party_id NUMBER;
2896 l_party_site_id NUMBER;
2897 l_is_delivery_required VARCHAR2(1);
2898 l_open_flag VARCHAR2(1) := 'Y'; -- prompt on conc pgm has been changed to "Include Closed Orders", so logic must change accordingly
2899
2900
2901 -- { Start of cursor definition for the Order Selection
2902 CURSOR c_order_for_customer IS
2903 SELECT header_id, sold_to_org_id, order_number, orig_sys_document_ref, order_source_id, change_sequence,org_id
2904 FROM oe_order_headers
2905 WHERE sold_to_org_id = p_customer_id
2906 -- AND open_flag = 'Y' -- only open orders are supported currently
2907 AND open_flag IN ('Y', l_open_flag)
2908 AND order_source_id = G_XML_ORDER_SOURCE_ID
2909 AND order_number BETWEEN nvl(p_so_number_from,order_number) AND nvl(p_so_number_to,order_number)
2910 AND ordered_date BETWEEN nvl(l_so_date_from,ordered_date) AND nvl(l_so_date_to + 1,ordered_date + 1)
2911 AND nvl(cust_po_number, -99) BETWEEN nvl(p_customer_po_no_from,nvl(cust_po_number, -99)) AND nvl(p_customer_po_no_to,nvl(cust_po_number, -99))
2912 AND org_id = nvl(p_operating_unit,org_id);
2913
2914 --
2915 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2916 --
2917 BEGIN
2918
2919 IF l_debug_level > 0 THEN
2920 oe_debug_pub.add( 'OEXPACKB: ENTERING PROCESS_SSO_CONC_PGM' ) ;
2921 END IF;
2922
2923 IF p_operating_unit IS NOT NULL THEN
2924 MO_GLOBAL.set_policy_context('S',p_operating_unit);
2925 END IF;
2926
2927 -- logic here is to reflect the fact that the p_open_orders_only parameter is now actually
2928 -- the input to the renamed prompt "Include Closed Orders". so if the of p_open_orders_only
2929 -- is 'Y', then we wish to include all orders (both open and closed).
2930
2931 IF p_open_orders_only = 'Y' then
2932 l_open_flag := 'N';
2933 END IF;
2934
2935
2936 l_so_date_from := fnd_date.canonical_to_date(p_so_date_from);
2937 l_so_date_to := fnd_date.canonical_to_date(p_so_date_to);
2938
2939 -----------------------------------------------------------
2940 -- Log Output file
2941 -----------------------------------------------------------
2942
2943 fnd_file.put_line(FND_FILE.OUTPUT, 'Show Sales Order Concurrent Program');
2944 fnd_file.put_line(FND_FILE.OUTPUT, '');
2945 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
2946 fnd_file.put_line(FND_FILE.OUTPUT, 'Customer id for Trading Partner : '|| p_customer_id);
2947 fnd_file.put_line(FND_FILE.OUTPUT, 'Open Orders Only: '|| p_open_orders_only);
2948 fnd_file.put_line(FND_FILE.OUTPUT, 'Sales Order Number From: '||p_so_number_from);
2949 fnd_file.put_line(FND_FILE.OUTPUT, 'Sales Order Number To: '||p_so_number_to);
2950 fnd_file.put_line(FND_FILE.OUTPUT, 'Sales Order Date From: '||p_so_date_from);
2951 fnd_file.put_line(FND_FILE.OUTPUT, 'Sales Order Date To: '||p_so_date_to);
2952 fnd_file.put_line(FND_FILE.OUTPUT, 'Customer PO number From: '||p_customer_po_no_from);
2953 fnd_file.put_line(FND_FILE.OUTPUT, 'Customer PO Number To: '||p_customer_po_no_to);
2954 fnd_file.put_line(FND_FILE.OUTPUT, 'Org Id: '||p_operating_unit);
2955 fnd_file.put_line(FND_FILE.OUTPUT, '');
2956 fnd_file.put_line(FND_FILE.OUTPUT,'Debug Level: '||l_debug_level);
2957
2958 -----------------------------------------------------------
2959 -- Setting Debug Mode and File
2960 -----------------------------------------------------------
2961
2962 If nvl(l_debug_level, 1) > 0 Then
2963 l_filename := oe_debug_pub.set_debug_mode ('FILE');
2964 fnd_file.put_line(FND_FILE.OUTPUT,'Debug File: ' || l_filename);
2965 fnd_file.put_line(FND_FILE.OUTPUT, '');
2966 END IF;
2967 l_filename := OE_DEBUG_PUB.set_debug_mode ('CONC');
2968
2969 -----------------------------------------------------------
2970 -- Get Concurrent Request Id
2971 -----------------------------------------------------------
2972
2973 IF l_debug_level > 0 THEN
2974 oe_debug_pub.add( 'BEFORE GETTING REQUEST ID' ) ;
2975 END IF;
2976 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
2977 IF l_debug_level > 0 THEN
2978 oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_REQUEST_ID ) ) ;
2979 END IF;
2980 fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
2981
2982 OPEN c_order_for_customer;
2983 FETCH c_order_for_customer BULK COLLECT
2984 INTO l_order_rec.header_id
2985 ,l_order_rec.sold_to_org_id
2986 ,l_order_rec.order_number
2987 ,l_order_rec.orig_sys_document_ref
2988 ,l_order_rec.order_source_id
2989 ,l_order_rec.change_sequence
2990 ,l_order_rec.org_id;
2991 CLOSE c_order_for_customer;
2992
2993 l_order_count := l_order_rec.header_id.count;
2994 IF l_debug_level > 0 THEN
2995 oe_debug_pub.add( 'COUNT: '||L_ORDER_COUNT ) ;
2996 END IF;
2997 IF l_order_count > 0 THEN
2998
2999 -----------------------------------------------------------
3000 -- Raise event for all orders fetched
3001 -----------------------------------------------------------
3002 FOR i IN 1..l_order_count LOOP
3003 IF l_debug_level > 0 THEN
3004 oe_debug_pub.add( 'BEFORE CALL TO OE_ACKNOWLEDGMENT_PUB.RAISE_EVENT_SHOWSO API: '||I , 2 ) ;
3005 oe_debug_pub.add( 'HEADER ID: '||L_ORDER_REC.HEADER_ID ( I ) , 1 ) ;
3006 oe_debug_pub.add( 'SOLD TO ORGID: '||L_ORDER_REC.SOLD_TO_ORG_ID ( I ) , 1 ) ;
3007 oe_debug_pub.add( 'SALES ORDER NUMBER: '||L_ORDER_REC.ORDER_NUMBER ( I ) , 1 ) ;
3008 oe_debug_pub.add( 'ORIG SYS DOCUMENT REF: '||L_ORDER_REC.ORIG_SYS_DOCUMENT_REF ( I ) , 1 ) ;
3009 oe_debug_pub.add( 'ORDER SOURCE ID: '||L_ORDER_REC.ORDER_SOURCE_ID ( I ) , 1 ) ;
3010 oe_debug_pub.add( 'ORG ID: '||L_ORDER_REC.ORG_ID ( I ) , 1 ) ;
3011 END IF;
3012
3013 Is_Delivery_Required( p_customer_id => p_customer_id,
3014 p_transaction_type => 'ONT',
3015 p_transaction_subtype => G_TRANSACTION_SSO,
3016 p_org_id => l_order_rec.org_id(i),
3017 x_party_id => l_party_id,
3018 x_party_site_id => l_party_site_id,
3019 x_is_delivery_required => l_is_delivery_required,
3020 x_return_status => l_return_status
3021 );
3022 If l_return_status <> FND_API.G_RET_STS_SUCCESS
3023 Or l_is_delivery_required = 'N' Then
3024 fnd_file.put_line(FND_FILE.OUTPUT,'Show SO not enabled for TP: ' || p_customer_id);
3025 If l_debug_level > 0 THEN
3026 oe_debug_pub.add( 'CONC PGM NOT RAISING SSO EVENT FOR TP: '|| p_customer_id);
3027 End If;
3028 Else
3029
3030 OE_ACKNOWLEDGMENT_PUB.Raise_Event_Showso (
3031 p_header_id => l_order_rec.header_id(i),
3032 p_line_id => null,
3033 p_customer_id => l_order_rec.sold_to_org_id(i),
3034 p_orig_sys_document_ref => l_order_rec.orig_sys_document_ref(i),
3035 p_change_sequence => l_order_rec.change_sequence(i),
3036 p_party_id => l_party_id,
3037 p_party_site_id => l_party_site_id,
3038 p_itemtype => OE_ORDER_IMPORT_WF.G_WFI_CONC_PGM,
3039 p_request_id => l_request_id,
3040 p_org_id => l_order_rec.org_id(i),
3041 x_return_status => l_return_status );
3042 IF l_debug_level > 0 THEN
3043 oe_debug_pub.add( 'AFTER OE_ACKNOWLEDGMENT_PUB.RAISE_EVENT_SHOWSO API: RETURN STATUS:'||L_RETURN_STATUS , 2 ) ;
3044 END IF;
3045
3046 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3047 fnd_file.put_line(FND_FILE.OUTPUT,'Not able to raise the event for Document Number : ' || l_order_rec.orig_sys_document_ref(i));
3048 fnd_file.put_line(FND_FILE.OUTPUT,'Error : ' || sqlerrm);
3049 l_return_status := FND_API.G_RET_STS_SUCCESS;
3050 ELSE
3051 fnd_file.put_line(FND_FILE.OUTPUT,'Successfully raised the event for Document Number : ' ||l_order_rec.orig_sys_document_ref(i));
3052 END IF;
3053
3054 End If;
3055 END LOOP;
3056 END IF;
3057 IF l_debug_level > 0 THEN
3058 oe_debug_pub.add( 'EXITING OE_ACKNOWLEDGMENT_PUB.PROCESS_SSO_CONC_PGM' , 2 ) ;
3059 END IF;
3060
3061 -- Exception Handling
3062 EXCEPTION
3063
3064 WHEN OTHERS THEN
3065 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3066 IF l_debug_level > 0 THEN
3067 oe_debug_pub.add( 'WHEN OTHERS - EXITING OE_ACKNOWLEDGMENT_PUB.PROCESS_SSO_CONC_PGM' , 2 ) ;
3068 oe_debug_pub.add( 'SQLERRM: '||SQLERRM||' SQLCODE:'||SQLCODE ) ;
3069 END IF;
3070 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3071 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'process_sso_conc_pgm');
3072 End if;
3073 End Process_SSO_CONC_PGM;
3074 -- End of the Process_SSO_Conc_Pgm }
3075
3076 Procedure Raise_Event_Xmlint
3077 (p_order_source_id IN Number,
3078 p_partner_document_num IN Varchar2,
3079 p_message_text IN Varchar2,
3080 p_document_num IN Number,
3081 p_order_type_id IN Number,
3082 p_change_sequence IN Varchar2,
3083 p_itemtype IN Varchar2,
3084 p_itemkey IN Number,
3085 p_transaction_type IN Varchar2,
3086 p_transaction_subtype IN Varchar2,
3087 p_doc_status IN Varchar2,
3088 p_org_id IN Number,
3089 p_sold_to_org_id IN Number,
3090 p_document_direction IN Varchar2,
3091 p_xmlg_document_id IN Number,
3092 p_xmlg_partner_type IN Varchar2,
3093 p_xmlg_party_id IN Number,
3094 p_xmlg_party_site_id IN Number,
3095 p_xmlg_icn IN Number,
3096 p_xmlg_msgid IN Varchar2,
3097 p_document_disposition IN Varchar2,
3098 p_conc_request_id IN Number,
3099 p_processing_stage IN Varchar2,
3100 p_response_flag IN Varchar2,
3101 p_header_id IN Number,
3102 p_subscriber_list IN Varchar2,
3103 p_line_ids IN Varchar2,
3104 p_failure_ack_flag IN Varchar2,
3105 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
3106 )
3107
3108 Is
3109
3110 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
3111 l_eventkey Number;
3112 l_message_text VARCHAR2(2000) := p_message_text;
3113 l_doc_status VARCHAR2(240) := p_doc_status;
3114 l_transaction_subtype VARCHAR2(30) := p_transaction_subtype;
3115 l_transaction_type VARCHAR2(30) := p_transaction_type;
3116 l_txn_token Varchar2(50);
3117 l_processing_stage VARCHAR2(30) := p_processing_stage;
3118 l_document_num NUMBER := p_document_num;
3119 l_order_type_id NUMBER := p_order_type_id;
3120 l_xmlg_icn NUMBER := p_xmlg_icn;
3121 l_header_id NUMBER := p_header_id;
3122 l_subscriber_list VARCHAR2(15) := nvl(p_subscriber_list,'ONT');
3123 --
3124 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3125 --
3126 l_converted VARCHAR2(100);
3127 i NUMBER;
3128 l_org_id NUMBER := p_org_id;
3129 l_conc_request_id NUMBER := p_conc_request_id;
3130 l_response_flag VARCHAR2(1) := p_response_flag;
3131 l_integ_profile VARCHAR2(10) := nvl (FND_PROFILE.VALUE ('ONT_EM_INTEG_SOURCES'), 'XML');
3132 l_customer_key_profile VARCHAR2(1) := 'N';
3133 l_failure_ack_flag VARCHAR2(1) := nvl(p_failure_ack_flag, 'N');
3134 l_order_processed_flag VARCHAR2(1) := 'Y';
3135
3136 Begin
3137 IF l_debug_level > 0 THEN
3138 oe_debug_pub.add( 'ENTERING RAISE_EVENT_XMLINT' ) ;
3139 END IF;
3140
3141 -- new profile to control which order sources can raise this event
3142 IF l_integ_profile = 'XML' THEN
3143 IF p_order_source_id <> G_XML_ORDER_SOURCE_ID THEN
3144 IF l_debug_level > 0 THEN
3145 oe_debug_pub.add( 'LEAVING RAISE_EVENT_XMLINT FOR DISABLED ORDER SOURCE: '|| p_order_source_id ) ;
3146 END IF;
3147 x_return_status := FND_API.G_RET_STS_SUCCESS;
3148 return;
3149 END IF;
3150 ELSIF l_integ_profile = 'EDIXML' THEN
3151 IF p_order_source_id NOT IN (G_XML_ORDER_SOURCE_ID, OE_GLOBALS.G_ORDER_SOURCE_EDI) THEN
3152 IF l_debug_level > 0 THEN
3153 oe_debug_pub.add( 'LEAVING RAISE_EVENT_XMLINT FOR DISABLED ORDER SOURCE: '|| p_order_source_id ) ;
3154 END IF;
3155 x_return_status := FND_API.G_RET_STS_SUCCESS;
3156 return;
3157 END IF;
3158 END IF;
3159
3160 IF l_debug_level > 0 THEN
3161 oe_debug_pub.add( 'ADDING PARAMETERS TO XML INTEGRATION EVENT' ) ;
3162 END IF;
3163
3164 --generating a unique event key
3165 SELECT OE_XML_MESSAGE_SEQ_S.nextval
3166 INTO l_eventkey
3167 FROM dual;
3168
3169
3170 -- { begin code release level
3171 IF OE_Code_Control.Code_Release_Level >= '110510' THEN
3172
3173 If l_subscriber_list <> 'DEFAULT' THEN
3174
3175 /* error checking primarily to guard against some OI cases where we
3176 get G_MISS_VALUES */
3177
3178 IF FND_API.G_MISS_NUM IN (p_order_source_id, p_sold_to_org_id) THEN
3179 l_parameter_list.DELETE;
3180 x_return_status := FND_API.G_RET_STS_SUCCESS;
3181
3182 IF l_debug_level > 0 THEN
3183 oe_debug_pub.add( 'NOT RAISING oracle.apps.ont.oi.xml_int.status without order source or sold to org id' ) ;
3184 oe_debug_pub.add( 'order source ' || p_order_source_id || ' or sold to org id' || p_sold_to_org_id ) ;
3185 oe_debug_pub.add( 'EXITING RAISE_EVENT_XMLINT' ) ;
3186 END IF;
3187 Return;
3188 END IF;
3189
3190 IF l_conc_request_id = FND_API.G_MISS_NUM THEN
3191 l_conc_request_id := NULL;
3192 l_converted := l_converted || 'CONC_REQUEST_ID, ';
3193 END IF;
3194 IF l_header_id = FND_API.G_MISS_NUM THEN
3195 l_header_id := NULL;
3196 l_converted := l_converted || 'HEADER_ID, ';
3197 END IF;
3198 IF l_org_id = FND_API.G_MISS_NUM THEN
3199
3200 /* SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
3201 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))) into l_org_id from DUAL; */
3202 l_org_id := MO_GLOBAL.Get_Current_Org_Id; --moac
3203 l_converted := l_converted || 'ORG_ID, ';
3204 END IF;
3205
3206 IF l_xmlg_icn IS NULL THEN
3207 l_xmlg_icn := l_eventkey; --reuse sequence value (mainly for generic OI)
3208 END IF;
3209
3210 IF l_doc_status = FND_API.G_RET_STS_SUCCESS THEN -- calls from Order Import Conc Pgm
3211 l_doc_status := 'SUCCESS';
3212 l_processing_stage := 'IMPORT_SUCCESS';
3213 ELSIF l_doc_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
3214 l_doc_status := 'ERROR';
3215 l_processing_stage := 'IMPORT_FAILURE';
3216 END IF;
3217
3218 IF l_transaction_subtype IS NULL THEN
3219 l_transaction_subtype := G_TRANSACTION_GENERIC;
3220 END IF;
3221
3222 IF l_message_text IS NULL THEN
3223 IF l_processing_stage = 'IMPORT_SUCCESS' THEN
3224 fnd_message.set_name ('ONT', 'OE_OI_IMPORT_SUCCESS_GEN');
3225 ELSIF l_processing_stage = 'IMPORT_FAILURE' THEN
3226 fnd_message.set_name ('ONT', 'OE_OI_IMPORT_FAILURE');
3227 END IF;
3228 l_txn_token := Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_subtype);
3229
3230 If l_txn_token IS NOT NULL Then
3231 fnd_message.set_token ('TRANSACTION',l_txn_token || ' -');
3232 Else
3233 fnd_message.set_token ('TRANSACTION', '');
3234 End If;
3235 l_message_text := fnd_message.get;
3236 END IF;
3237
3238 END IF; -- end of branch on subscriber list
3239
3240 END IF; -- } end code release level
3241 -----------------------------------------------------------
3242 -- Non-CLN params
3243 -----------------------------------------------------------
3244 IF OE_Code_Control.Code_Release_Level >= '110510' THEN
3245 IF l_processing_stage = 'IMPORT_FAILURE' THEN
3246 IF p_order_source_id = 20 AND l_transaction_subtype IN (G_TRANSACTION_CHO, G_TRANSACTION_CPO) THEN
3247 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
3248 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
3249
3250 Begin
3251 Select order_number, order_type_id, header_id
3252 Into l_document_num, l_order_type_id, l_header_id
3253 From oe_order_headers
3254 Where orig_sys_document_ref = p_partner_document_num
3255 And order_source_id = 20
3256 And decode(l_customer_key_profile, 'Y',
3257 nvl(sold_to_org_id, -999), 1)
3258 = decode(l_customer_key_profile, 'Y',
3259 nvl(p_sold_to_org_id, -999), 1);
3260
3261 Exception
3262 When Others Then
3263 IF l_debug_level > 0 THEN
3264 oe_debug_pub.add( 'EXCEPTION WHEN FETCH ORDER NUM' ) ;
3265 END IF;
3266
3267 -- could not derive info
3268 l_document_num := NULL;
3269 l_order_type_id := NULL;
3270 l_header_id := NULL;
3271 End;
3272
3273 ELSE
3274 l_document_num := NULL;
3275 l_order_type_id := NULL;
3276 l_header_id := NULL;
3277 END IF;
3278 END IF;
3279
3280 -- order source
3281 wf_event.AddParameterToList(p_name=> 'ORDER_SOURCE_ID',
3282 p_value=> p_order_source_id,
3283 p_parameterlist=> l_parameter_list);
3284
3285 -- item_type, item_key under which the inbound/outbound XML is being processed
3286 wf_event.AddParameterToList(p_name=> 'WF_ITEM_TYPE',
3287 p_value=> p_itemtype,
3288 p_parameterlist=> l_parameter_list);
3289 wf_event.AddParameterToList(p_name=> 'WF_ITEM_KEY',
3290 p_value=> p_itemkey,
3291 p_parameterlist=> l_parameter_list);
3292
3293 wf_event.AddParameterToList(p_name=> 'SOLD_TO_ORG_ID',
3294 p_value=> p_sold_to_org_id,
3295 p_parameterlist=> l_parameter_list);
3296
3297 wf_event.AddParameterToList(p_name=> 'PROCESSING_STAGE',
3298 p_value=> l_processing_stage,
3299 p_parameterlist=> l_parameter_list);
3300
3301 wf_event.AddParameterToList(p_name=> 'CONC_REQUEST_ID',
3302 p_value=> l_conc_request_id,
3303 p_parameterlist=> l_parameter_list);
3304
3305 IF p_order_source_id <> G_XML_ORDER_SOURCE_ID THEN
3306 l_response_flag := NULL;
3307 END IF;
3308 wf_event.AddParameterToList(p_name=> 'RESPONSE_FLAG',
3309 p_value=> l_response_flag,
3310 p_parameterlist=> l_parameter_list);
3311
3312 wf_event.AddParameterToList(p_name=> 'HEADER_ID',
3313 p_value=> l_header_id,
3314 p_parameterlist=> l_parameter_list);
3315
3316 END IF;
3317 -----------------------------------------------------------------
3318 -- CLN Params
3319 -----------------------------------------------------------------
3320 -----------------------------------------------------------------
3321 -- START CLN KEY PARAMS
3322 -----------------------------------------------------------------
3323
3324 If ((l_transaction_subtype = Oe_Acknowledgment_Pub.G_TRANSACTION_POI) OR
3325 (l_transaction_subtype = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO) OR
3326 (l_transaction_subtype = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO) OR
3327 (l_transaction_subtype = Oe_Acknowledgment_Pub.G_TRANSACTION_850) OR
3328 (l_transaction_subtype = Oe_Acknowledgment_Pub.G_TRANSACTION_860) OR
3329 (l_transaction_subtype = Oe_Acknowledgment_Pub.G_TRANSACTION_GENERIC)) Then
3330
3331 /* l_internal_control_number := wf_engine.GetItemAttrNumber( p_itemtype
3332 , p_itemkey
3333 , 'PARAMETER5'
3334 );*/
3335
3336 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_CONTROL_NUMBER',
3337 p_value=> l_xmlg_icn,
3338 p_parameterlist=> l_parameter_list);
3339
3340
3341 -- start bug 4179657
3342 IF l_processing_stage <> 'IMPORT_SUCCESS' THEN
3343 l_order_processed_flag := 'N';
3344 END IF;
3345 -- end bug 4179657
3346
3347 Else
3348 -- Outbound Transactions
3349 /* l_xml_message_id := wf_engine.GetItemAttrText( p_itemtype
3350 , p_itemkey
3351 , 'ECX_MSGID_ATTR'
3352 , TRUE
3353 );*/
3354 wf_event.AddParameterToList(p_name=> 'XMLG_MESSAGE_ID',
3355 p_value=> p_xmlg_msgid,
3356 p_parameterlist=> l_parameter_list);
3357
3358 -- If the xml message id exists, then the document was successfully
3359 -- sent. However, if we are raising the event prior to document_send
3360 -- then we need to populate the alternate CLN key
3361 -- of these we populate the XMLG_INTERNAL_TXN_TYPE/SUBTYPE always (as it
3362 -- is part of the key for the history table),
3363 -- the other parameters are added in this code block
3364 IF OE_Code_Control.Code_Release_Level >= '110510' THEN
3365 wf_event.AddParameterToList(p_name=> 'DOCUMENT_DIRECTION',
3366 p_value=> p_document_direction,
3367 p_parameterlist=> l_parameter_list);
3368
3369 wf_event.AddParameterToList(p_name=> 'XMLG_DOCUMENT_ID',
3370 p_value=> p_xmlg_document_id,
3371 p_parameterlist=> l_parameter_list);
3372
3373 wf_event.AddParameterToList(p_name=> 'TRADING_PARTNER_TYPE',
3374 p_value=> p_xmlg_partner_type,
3375 p_parameterlist=> l_parameter_list);
3376
3377 wf_event.AddParameterToList(p_name=> 'TRADING_PARTNER_ID',
3378 p_value=> p_xmlg_party_id,
3379 p_parameterlist=> l_parameter_list);
3380
3381 wf_event.AddParameterToList(p_name=> 'TRADING_PARTNER_SITE',
3382 p_value=> p_xmlg_party_site_id,
3383 p_parameterlist=> l_parameter_list);
3384
3385 -- start bug 4179657
3386 -- if this flag is N, then it means that the ack
3387 -- is being sent for a failure case, so the subscription
3388 -- needs to ignore the order number, header_id and order_type_id
3389 IF l_failure_ack_flag = 'Y' THEN
3390 l_order_processed_flag := 'N';
3391 END IF;
3392 -- end bug 4179657
3393
3394 END IF;
3395 End if;
3396 -----------------------------------------------------------
3397 -- START CLN OPTIONAL PARAMS
3398 -----------------------------------------------------------
3399 --adding partner document number
3400
3401 wf_event.AddParameterToList(p_name=> 'PARTNER_DOCUMENT_NO',
3402 p_value=> p_partner_document_num,
3403 p_parameterlist=> l_parameter_list);
3404 --adding document number
3405
3406 wf_event.AddParameterToList(p_name=> 'DOCUMENT_NO',
3407 p_value=> l_document_num,
3408 p_parameterlist=> l_parameter_list);
3409
3410 --adding message text
3411
3412 wf_event.AddParameterToList(p_name=> 'MESSAGE_TEXT',
3413 p_value=> l_message_text,
3414 p_parameterlist=> l_parameter_list);
3415
3416 IF OE_Code_Control.Code_Release_Level >= '110510' THEN
3417 --transaction type
3418 IF l_transaction_subtype = G_TRANSACTION_CBODO THEN
3419 l_transaction_type := 'ECX';
3420 -- begin bug 4179657
3421 l_order_processed_flag := 'N';
3422 -- end bug 4179657
3423 END IF;
3424
3425 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_TXN_TYPE',
3426 p_value=> l_transaction_type,
3427 p_parameterlist=> l_parameter_list);
3428
3429 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_TXN_SUBTYPE',
3430 p_value=> l_transaction_subtype,
3431 p_parameterlist=> l_parameter_list);
3432
3433 -- these changes for CLN, per bug 3103495
3434
3435 -- We will use the OM_STATUS parameter to report ACTIVE SUCCESS ERROR statuses
3436 -- Since CLN does not support ACTIVE, we will change that value to SUCCESS for
3437 -- CLN's DOC_STATUS
3438 wf_event.AddParameterToList(p_name=> 'ONT_DOC_STATUS',
3439 p_value=> l_doc_status,
3440 p_parameterlist=> l_parameter_list);
3441
3442
3443 IF l_doc_status = 'ACTIVE' THEN
3444 l_doc_status := 'SUCCESS';
3445 END IF;
3446
3447 wf_event.AddParameterToList(p_name=> 'DOCUMENT_STATUS',
3448 p_value=> l_doc_status,
3449 p_parameterlist=> l_parameter_list);
3450
3451 -- We also need to publish a subscriber list
3452 -- condition modified for bug 3433024
3453 IF p_order_source_id = OE_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID
3454 AND l_subscriber_list <> 'DEFAULT'
3455 AND l_processing_stage NOT IN ('OUTBOUND_TRIGGERED', 'OUTBOUND_SETUP') THEN
3456 l_subscriber_list := l_subscriber_list || ',CLN';
3457 END IF;
3458 wf_event.AddParameterToList(p_name=> 'SUBSCRIBER_LIST',
3459 p_value=> l_subscriber_list,
3460 p_parameterlist=> l_parameter_list);
3461
3462 wf_event.AddParameterToList(p_name=> 'DOCUMENT_REVISION_NO',
3463 p_value=> p_change_sequence,
3464 p_parameterlist=> l_parameter_list);
3465
3466 wf_event.AddParameterToList(p_name=> 'ORG_ID',
3467 p_value=> l_org_id,
3468 p_parameterlist=> l_parameter_list);
3469
3470
3471 wf_event.AddParameterToList(p_name=> 'ORDER_TYPE_ID',
3472 p_value=> l_order_type_id,
3473 p_parameterlist=> l_parameter_list);
3474
3475 If l_subscriber_list = 'DEFAULT' THEN
3476 wf_event.AddParameterToList(p_name=> 'LINE_IDS',
3477 p_value=> p_line_ids,
3478 p_parameterlist=> l_parameter_list);
3479 Else
3480 -- begin bug 4179657
3481 wf_event.AddParameterToList(p_name=> 'ORDER_PROCESSED_FLAG',
3482 p_value=> l_order_processed_flag,
3483 p_parameterlist=> l_parameter_list);
3484 -- end bug 4179657
3485 End If;
3486
3487 END IF;
3488
3489
3490 IF l_debug_level > 0 THEN
3491 oe_debug_pub.add( 'FINISHED ADDING PARAMETERS TO INTEGRATION EVENT' ) ;
3492 FOR i in 1..l_parameter_list.count LOOP
3493 oe_debug_pub.add (' ' || l_parameter_list(i).name || ' : ' || l_parameter_list(i).value);
3494 END LOOP;
3495 oe_debug_pub.add( 'FINISHED PRINTING EVENT PARAMS' ) ;
3496 oe_debug_pub.add( 'CONVERTED LIST ' || l_converted ) ;
3497 oe_debug_pub.add( 'BEFORE RAISE EVENT oracle.apps.ont.oi.xml_int.status' ) ;
3498 END IF;
3499
3500 IF OE_Code_Control.Code_Release_Level < '110510' THEN
3501 IF p_order_source_id <> G_XML_ORDER_SOURCE_ID OR
3502 l_processing_stage NOT IN ('IMPORT_SUCCESS', 'OUTBOUND_SENT') OR
3503 l_transaction_subtype = G_TRANSACTION_CBODO THEN
3504 l_parameter_list.DELETE;
3505 x_return_status := FND_API.G_RET_STS_SUCCESS;
3506
3507 IF l_debug_level > 0 THEN
3508 oe_debug_pub.add( 'NOT RAISING oracle.apps.ont.oi.xml_int.status for this pre-110510 case' ) ;
3509 oe_debug_pub.add( 'EXITING RAISE_EVENT_XMLINT' ) ;
3510 END IF;
3511 Return; -- thus we guarantee that old behaviour is preserved
3512 END IF;
3513 END IF;
3514
3515 wf_event.raise( p_event_name => 'oracle.apps.ont.oi.xml_int.status',
3516 p_event_key => l_eventkey,
3517 p_parameters => l_parameter_list);
3518
3519
3520 IF l_debug_level > 0 THEN
3521 oe_debug_pub.add( 'AFTER RAISE EVENT oracle.apps.ont.oi.xml_int.status' ) ;
3522 oe_debug_pub.add( 'EXITING RAISE_EVENT_XMLINT' ) ;
3523 END IF;
3524
3525
3526 l_parameter_list.DELETE;
3527
3528 x_return_status := FND_API.G_RET_STS_SUCCESS;
3529 EXCEPTION
3530 When Others Then
3531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3532 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3533 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint');
3534 END IF;
3535 IF l_debug_level > 0 THEN
3536 oe_debug_pub.add( 'WHEN OTHERS EXCEPTION IN RAISE_EVENT_XMLINT_WF' ) ;
3537 END IF;
3538 End Raise_Event_Xmlint;
3539 --End of Procedure to raise XML Integration (CLN) Event
3540
3541
3542 /*----------------------------------------------------------------------
3543 Applies 3A7 change notification hold based on the hold id passed. Uses
3544 standard Hold APIs.
3545 ----------------------------------------------------------------------*/
3546 Procedure Apply_3A7_Hold
3547 ( p_header_id IN NUMBER
3548 , p_line_id IN NUMBER
3549 , p_sold_to_org_id IN NUMBER
3550 , p_tp_check IN VARCHAR2
3551 , x_return_status OUT NOCOPY VARCHAR2
3552 )
3553 IS
3554 l_tp_check VARCHAR2(1) := nvl(p_tp_check, FND_API.G_TRUE);
3555 l_msg_count NUMBER := 0;
3556 l_msg_data VARCHAR2(2000);
3557 l_return_status VARCHAR2(30) := fnd_api.g_ret_sts_success;
3558 l_hold_result VARCHAR2(30);
3559 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
3560 l_is_delivery_required_cso VARCHAR2(1) := 'N';
3561 l_hold_id NUMBER := 56;
3562 l_party_id NUMBER;
3563 l_party_site_id NUMBER;
3564 --
3565 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3566 --
3567 BEGIN
3568 IF l_debug_level > 0 THEN
3569 oe_debug_pub.add( 'Entering Apply_3A7_Hold' ) ;
3570 END IF;
3571
3572 IF l_tp_check = FND_API.G_TRUE THEN
3573 IF l_debug_level > 0 THEN
3574 oe_debug_pub.add( 'Before Calling OE_Acknowledgment_PUB.Is_Delivery_Required') ;
3575 END IF;
3576
3577 Is_Delivery_Required
3578 ( p_customer_id => p_sold_to_org_id,
3579 p_transaction_type => 'ONT',
3580 p_transaction_subtype => G_TRANSACTION_CSO,
3581 x_party_id => l_party_id,
3582 x_party_site_id => l_party_site_id,
3583 x_is_delivery_required => l_is_delivery_required_cso,
3584 x_return_status => l_return_status
3585 );
3586
3587 IF l_debug_level > 0 THEN
3588 oe_debug_pub.add( 'After Calling OE_Acknowledgment_PUB.Is_Delivery_Required: is delivery required for 3a7 = ' || l_is_delivery_required_cso||': Return Status:'||l_return_status ) ;
3589 END IF;
3590 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3591 IF l_is_delivery_required_cso = 'Y' THEN
3592 IF l_debug_level > 0 THEN
3593 oe_debug_pub.add('3A7 enabled transaction. Hold should be applied');
3594 END IF;
3595 ELSE
3596 IF l_debug_level > 0 THEN
3597 oe_debug_pub.add('NOT 3A7 enabled transaction. Hold should NOT be applied');
3598 END IF;
3599 RETURN;
3600 END IF;
3601 END IF;
3602 ELSE
3603 IF l_debug_level > 0 THEN
3604 oe_debug_pub.add( 'Not performing TP check for 3A7') ;
3605 END IF;
3606 END IF;
3607
3608 -- Check if Hold already exists on this order line
3609 IF l_debug_level > 0 THEN
3610 oe_debug_pub.add( 'Check If Hold Already Applied' ) ;
3611 END IF;
3612
3613 OE_HOLDS_PUB.Check_Holds
3614 ( p_api_version => 1.0
3615 , p_header_id => p_header_id
3616 , p_line_id => p_line_id
3617 , p_hold_id => l_hold_id
3618 , p_entity_code => 'O'
3619 , p_entity_id => p_header_id
3620 , x_result_out => l_hold_result
3621 , x_msg_count => l_msg_count
3622 , x_msg_data => l_msg_data
3623 , x_return_status => l_return_status
3624 );
3625
3626 -- Return with Success if this Hold Already exists on the order line
3627 IF l_hold_result = FND_API.G_TRUE THEN
3628 IF l_debug_level > 0 THEN
3629 oe_debug_pub.add( ' Hold Already Applied On Header Id:' || P_HEADER_ID||': Line_Id:'||p_line_id) ;
3630 END IF;
3631 RETURN ;
3632 END IF ;
3633
3634 -- Apply 3A7 Change Notification Hold on Order line
3635 IF l_debug_level > 0 THEN
3636 oe_debug_pub.add( 'Applying 3A7 Change Notification Hold on LINE ID:' || P_LINE_ID) ;
3637 END IF;
3638
3639 l_hold_source_rec.hold_id := l_hold_id ; -- 3A7 Hold
3640 l_hold_source_rec.hold_entity_code:= 'O'; -- Order Hold
3641 l_hold_source_rec.hold_entity_id := p_header_id; -- Order Header
3642 l_hold_source_rec.header_id := p_header_id; -- Order Header
3643 l_hold_source_rec.line_id := p_line_id; -- Order line
3644 OE_Holds_PUB.Apply_Holds
3645 ( p_api_version => 1.0
3646 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
3647 , p_hold_source_rec => l_hold_source_rec
3648 , x_msg_count => l_msg_count
3649 , x_msg_data => l_msg_data
3650 , x_return_status => l_return_status
3651 );
3652
3653 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3654 RAISE FND_API.G_EXC_ERROR;
3655 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3657 END IF;
3658
3659 x_return_status := FND_API.G_RET_STS_SUCCESS;
3660
3661 IF l_debug_level > 0 THEN
3662 oe_debug_pub.add( 'Applied 3a7 Hold On Header Id:' || P_HEADER_ID||':Line_Id:'||p_line_id , 3 ) ;
3663 END IF;
3664
3665 EXCEPTION
3666
3667 WHEN FND_API.G_EXC_ERROR THEN
3668 IF l_debug_level > 0 THEN
3669 oe_debug_pub.add('In G_EXC_ERROR exception - Apply_3A7_hold');
3670 END IF;
3671 x_return_status := FND_API.G_RET_STS_ERROR;
3672 OE_MSG_PUB.Count_And_Get
3673 ( p_count => l_msg_count,
3674 p_data => l_msg_data
3675 );
3676
3677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3678 IF l_debug_level > 0 THEN
3679 oe_debug_pub.add('In G_EXC_UNEXPECTED_ERROR exception - Apply_3A7_hold');
3680 END IF;
3681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3682 OE_MSG_PUB.Count_And_Get
3683 ( p_count => l_msg_count,
3684 p_data => l_msg_data
3685 );
3686
3687 WHEN OTHERS THEN
3688 IF l_debug_level > 0 THEN
3689 oe_debug_pub.add('In when others exception - Apply_3A7_hold');
3690 END IF;
3691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3692 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3693 THEN
3694 FND_MSG_PUB.Add_Exc_Msg
3695 ( G_PKG_NAME
3696 , 'Apply_3A7_Hold'
3697 );
3698 END IF;
3699
3700 OE_MSG_PUB.Count_And_Get
3701 ( p_count => l_msg_count,
3702 p_data => l_msg_data
3703 );
3704
3705 END Apply_3A7_Hold;
3706
3707 /*----------------------------------------------------------------------
3708 Releases 3A7 change notification hold on the order. Uses standard Hold APIs.
3709 ----------------------------------------------------------------------*/
3710 Procedure Release_3A7_Hold
3711 ( p_header_id IN NUMBER
3712 , p_line_id IN NUMBER
3713 , x_return_status OUT NOCOPY VARCHAR2
3714 )
3715 IS
3716 l_hold_id NUMBER := 56;
3717 l_hold_exists VARCHAR2(1);
3718 l_msg_count NUMBER := 0;
3719 l_msg_data VARCHAR2(2000);
3720 l_return_status VARCHAR2(30) := fnd_api.g_ret_sts_success;
3721 l_release_reason VARCHAR2(30);
3722 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
3723 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
3724 l_hold_result VARCHAR2(30);
3725 --
3726 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3727 --
3728 BEGIN
3729
3730 IF l_debug_level > 0 THEN
3731 oe_debug_pub.add( 'In Release_3A7_Hold' , 3 ) ;
3732 END IF;
3733
3734 -- Checking Existence Of 3A7 Hold
3735 OE_HOLDS_PUB.Check_Holds
3736 ( p_api_version => 1.0
3737 , p_header_id => p_header_id
3738 , p_line_id => p_line_id
3739 , p_hold_id => l_hold_id
3740 , p_entity_code => 'O'
3741 , p_entity_id => p_header_id
3742 , x_result_out => l_hold_result
3743 , x_msg_count => l_msg_count
3744 , x_msg_data => l_msg_data
3745 , x_return_status => l_return_status
3746 );
3747
3748 -- Return with Success if this Hold exists on the order line
3749 IF l_hold_result = FND_API.G_TRUE THEN
3750 IF l_debug_level > 0 THEN
3751 oe_debug_pub.add( ' Hold Exists On Header Id:' || P_HEADER_ID||': Line_Id:'||p_line_id) ;
3752 END IF;
3753 l_hold_exists := 'Y';
3754 END IF ;
3755
3756 IF l_hold_exists = 'Y' THEN
3757 IF l_debug_level > 0 THEN
3758 oe_debug_pub.add('Releasing 3a7 Hold On Order Header Id:' || p_header_ID ||':Line_Id:'||p_line_id, 3 ) ;
3759 END IF;
3760 l_hold_source_rec.hold_id := l_hold_id;
3761 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
3762 l_hold_source_rec.HOLD_ENTITY_ID := p_header_id; -- Order Header
3763 l_hold_source_rec.header_id := p_header_id; -- Order Header
3764 l_hold_source_rec.line_id := p_line_id; -- Order line
3765 l_hold_release_rec.release_reason_code := '3A7_RESPONSE_RECEIVED';
3766
3767 OE_Holds_PUB.Release_Holds
3768 ( p_api_version => 1.0
3769 , p_hold_source_rec => l_hold_source_rec
3770 , p_hold_release_rec => l_hold_release_rec
3771 , x_msg_count => l_msg_count
3772 , x_msg_data => l_msg_data
3773 , x_return_status => l_return_status
3774 );
3775
3776 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3777 RAISE FND_API.G_EXC_ERROR;
3778 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3780 END IF;
3781 IF l_debug_level > 0 THEN
3782 oe_debug_pub.add( 'Released 3a7 Hold On Header Id:' || P_HEADER_ID ||':Line_Id:'||p_line_id, 3 ) ;
3783 END IF;
3784
3785 END IF; -- hold exists
3786 x_return_status := FND_API.G_RET_STS_SUCCESS;
3787 IF l_debug_level > 0 THEN
3788 oe_debug_pub.add( 'Exiting Release_3A7 Hold', 3 ) ;
3789 END IF;
3790
3791 EXCEPTION
3792
3793 WHEN FND_API.G_EXC_ERROR THEN
3794 IF l_debug_level > 0 THEN
3795 oe_debug_pub.add('In G_EXC_ ERROR exception - Release_3A7_hold');
3796 END IF;
3797 x_return_status := FND_API.G_RET_STS_ERROR;
3798 OE_MSG_PUB.Count_And_Get
3799 ( p_count => l_msg_count,
3800 p_data => l_msg_data
3801 );
3802
3803 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3804 IF l_debug_level > 0 THEN
3805 oe_debug_pub.add('In G_EXC_UNEXPECTED_ERROR exception - Release_3A7_hold');
3806 END IF;
3807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3808 OE_MSG_PUB.Count_And_Get
3809 ( p_count => l_msg_count,
3810 p_data => l_msg_data
3811 );
3812
3813 WHEN OTHERS THEN
3814 IF l_debug_level > 0 THEN
3815 oe_debug_pub.add('In when others exception - Release_3A7_hold');
3816 END IF;
3817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3818 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3819 THEN
3820 FND_MSG_PUB.Add_Exc_Msg
3821 ( G_PKG_NAME
3822 , 'Release_3A7_Hold'
3823 );
3824 END IF;
3825
3826 OE_MSG_PUB.Count_And_Get
3827 ( p_count => l_msg_count,
3828 p_data => l_msg_data
3829 );
3830
3831 END Release_3A7_Hold;
3832
3833 /*----------------------------------------------------------------------
3834 Processes 3A8 transaction received. This will be called during process order call
3835 ----------------------------------------------------------------------*/
3836 Procedure Process_3A8
3837 ( p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
3838 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
3839 , x_return_status OUT NOCOPY VARCHAR2
3840 )
3841 IS
3842 l_msg_count NUMBER := 0;
3843 l_msg_data VARCHAR2(2000);
3844 l_return_status VARCHAR2(30) := fnd_api.g_ret_sts_success;
3845 l_release_3a7_hold_flag VARCHAR2(1) := 'N';
3846 --
3847 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3848 --
3849 BEGIN
3850
3851 IF l_debug_level > 0 THEN
3852 oe_debug_pub.add( 'In OE_Acknowledgment_Pub.Process_3A8' , 3 ) ;
3853 END IF;
3854
3855 /* 1) If independent 3A8 then release the hold.
3856 2) If response 3A8 then
3857 a) Release the hold if columns match or qty is zero.
3858 b) Otherwise, raise an error. */
3859
3860 IF NVL(p_x_line_rec.cso_response_flag, 'N') = 'N' then -- independent 3A8
3861 IF l_debug_level > 0 THEN
3862 oe_debug_pub.add( 'Independent 3A8/3A9 received, releasing hold', 3 ) ;
3863 END IF;
3864 l_release_3a7_hold_flag :='Y';
3865 ELSE -- it is 3A8 response
3866 IF l_debug_level > 0 THEN
3867 oe_debug_pub.add('3A8 response attrs');
3868 oe_debug_pub.add('Old qty: ' || p_old_line_rec.ordered_quantity || ' New qty:' || p_x_line_rec.ordered_quantity);
3869 oe_debug_pub.add('Old UOM: ' || p_old_line_rec.order_quantity_uom || ' New UOM:' || p_x_line_rec.order_quantity_uom);
3870 oe_debug_pub.add('Old ssdt: ' || p_old_line_rec.schedule_ship_date || ' New ssdt:' || p_x_line_rec.schedule_ship_date);
3871 oe_debug_pub.add('Old usp: ' || p_old_line_rec.unit_selling_price || ' New cinp:' || p_x_line_rec.customer_item_net_price);
3872 END IF;
3873 IF (p_x_line_rec.ordered_quantity = 0) OR
3874 (p_x_line_rec.ordered_quantity = p_old_line_rec.ordered_quantity AND
3875 p_x_line_rec.order_quantity_uom= p_old_line_rec.order_quantity_uom AND
3876 p_x_line_rec.schedule_ship_date= p_old_line_rec.schedule_ship_date AND
3877 -- p_x_line_rec.unit_selling_price= p_old_line_rec.unit_selling_price) THEN
3878 nvl(p_x_line_rec.customer_item_net_price, p_x_line_rec.unit_selling_price) = p_old_line_rec.unit_selling_price) THEN
3879 IF l_debug_level > 0 THEN
3880 oe_debug_pub.add( '3A8 response received, columns matching or zero quantity- releasing hold. ordered_quantity:' || p_x_line_rec.ordered_quantity, 3 ) ;
3881
3882 END IF;
3883 l_release_3a7_hold_flag :='Y';
3884 ELSE -- columns does not match and quantity <> 0
3885 IF l_debug_level > 0 THEN
3886 oe_debug_pub.add( '3A8 response received, columns does not match and qty <> 0, Raise an error', 3 ) ;
3887 END IF;
3888 FND_MESSAGE.SET_NAME('ONT', 'ONT_3A8_RESPONSE_COL_MISMATCH');
3889 OE_MSG_PUB.Add;
3890 RAISE FND_API.G_EXC_ERROR;
3891 END IF; -- check for columns
3892 END IF; -- check for independent/response 3A8
3893
3894 IF l_release_3a7_hold_flag = 'Y' THEN
3895 IF l_debug_level > 0 THEN
3896 oe_debug_pub.add( 'Releasing hold');
3897 END IF;
3898 Release_3A7_Hold
3899 ( p_header_id => p_x_line_rec.header_id
3900 , p_line_id => p_x_line_rec.line_id
3901 , x_return_status => l_return_status
3902 );
3903 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3904 RAISE FND_API.G_EXC_ERROR;
3905 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3907 END IF;
3908
3909 END IF;
3910
3911 x_return_status := FND_API.G_RET_STS_SUCCESS;
3912 IF l_debug_level > 0 THEN
3913 oe_debug_pub.add( 'Exiting Process_3A8', 3 ) ;
3914 END IF;
3915
3916 EXCEPTION
3917
3918 WHEN FND_API.G_EXC_ERROR THEN
3919 IF l_debug_level > 0 THEN
3920 oe_debug_pub.add('In G_EXC_ ERROR exception - Process_3A8');
3921 END IF;
3922 x_return_status := FND_API.G_RET_STS_ERROR;
3923 OE_MSG_PUB.Count_And_Get
3924 ( p_count => l_msg_count,
3925 p_data => l_msg_data
3926 );
3927
3928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3929 IF l_debug_level > 0 THEN
3930 oe_debug_pub.add('In G_EXC_UNEXPECTED_ERROR exception - Process_3A8');
3931 END IF;
3932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3933 OE_MSG_PUB.Count_And_Get
3934 ( p_count => l_msg_count,
3935 p_data => l_msg_data
3936 );
3937
3938 WHEN OTHERS THEN
3939 IF l_debug_level > 0 THEN
3940 oe_debug_pub.add('In when others exception - Process_3A8');
3941 END IF;
3942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3943 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3944 THEN
3945 FND_MSG_PUB.Add_Exc_Msg
3946 ( G_PKG_NAME
3947 , 'Process_3A8'
3948 );
3949 END IF;
3950
3951 OE_MSG_PUB.Count_And_Get
3952 ( p_count => l_msg_count,
3953 p_data => l_msg_data
3954 );
3955
3956 END Process_3A8;
3957
3958
3959 /*----------------------------------------------------------------------
3960 Helper procedure to get the message token for a particular txn type
3961 ----------------------------------------------------------------------*/
3962 FUNCTION EM_Transaction_Type
3963 ( p_txn_code IN VARCHAR2
3964 ) RETURN VARCHAR2
3965 IS
3966 l_transaction_type VARCHAR2(80);
3967 --
3968 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3969 --
3970 BEGIN
3971
3972 IF p_txn_code IS NULL
3973 THEN
3974 RETURN NULL;
3975 END IF;
3976
3977 SELECT MEANING
3978 INTO l_transaction_type
3979 FROM OE_LOOKUPS
3980 WHERE LOOKUP_CODE = p_txn_code
3981 AND LOOKUP_TYPE = 'ONT_ELECMSGS_TYPES';
3982
3983 RETURN l_transaction_type;
3984
3985 EXCEPTION
3986 WHEN OTHERS THEN
3987 RETURN NULL;
3988
3989 End EM_Transaction_Type;
3990
3991 Procedure Raise_CBOD_Out_Event
3992 ( p_orig_sys_document_ref IN Varchar2,
3993 p_sold_to_org_id IN Number,
3994 p_change_sequence IN Varchar2,
3995 p_icn IN Number,
3996 p_org_id IN Number,
3997 p_transaction_type IN Varchar2,
3998 p_confirmation_flag IN Varchar2,
3999 p_cbod_message_text IN Varchar2,
4000 x_return_status OUT NOCOPY VARCHAR2)
4001 IS
4002 l_user_key VARCHAR2(240);
4003 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
4004 l_eventkey Number;
4005 BEGIN
4006
4007 l_user_key := p_orig_sys_document_ref || ',' || to_char(p_sold_to_org_id) || ',' || p_change_sequence || ',' || p_transaction_type;
4008
4009 If p_confirmation_flag = '2' Then
4010 wf_event.AddParameterToList(p_name=> 'ORIG_SYS_DOCUMENT_REF',
4011 p_value=> p_orig_sys_document_ref,
4012 p_parameterlist=> l_parameter_list);
4013 wf_event.AddParameterToList(p_name=> 'PARAMETER4',
4014 p_value=> p_sold_to_org_id,
4015 p_parameterlist=> l_parameter_list);
4016 wf_event.AddParameterToList(p_name=> 'PARAMETER5',
4017 p_value=> p_icn,
4018 p_parameterlist=> l_parameter_list);
4019 wf_event.AddParameterToList(p_name=> 'PARAMETER6',
4020 p_value=> p_cbod_message_text,
4021 p_parameterlist=> l_parameter_list);
4022 wf_event.AddParameterToList(p_name=> 'USER_KEY',
4023 p_value=> l_user_key,
4024 p_parameterlist=> l_parameter_list);
4025 wf_event.AddParameterToList(p_name=> 'ORG_ID',
4026 p_value=> p_org_id,
4027 p_parameterlist=> l_parameter_list);
4028 wf_event.AddParameterToList(p_name=> 'PARAMETER7',
4029 p_value=> p_change_sequence,
4030 p_parameterlist=> l_parameter_list);
4031
4032 SELECT OE_XML_MESSAGE_SEQ_S.nextval
4033 INTO l_eventkey
4034 FROM dual;
4035
4036 wf_event.raise( p_event_name => 'oracle.apps.ont.oi.cbod_out.confirm',
4037 p_event_key => l_eventkey,
4038 p_parameters => l_parameter_list);
4039
4040 x_return_status := FND_API.G_RET_STS_SUCCESS;
4041 Else
4042 x_return_status := FND_API.G_RET_STS_ERROR;
4043 End If;
4044
4045 l_parameter_list.DELETE;
4046
4047 Exception
4048 When Others Then
4049 l_parameter_list.DELETE;
4050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4051
4052 END Raise_CBOD_Out_Event;
4053
4054 -- Procedure added for bug 9685021
4055
4056 Procedure is_line_exists(p_line_id IN NUMBER,x_exists_flag OUT NOCOPY VARCHAR2)
4057 IS
4058 PRAGMA AUTONOMOUS_TRANSACTION;
4059 temp VARCHAR2(1);
4060 BEGIN
4061 SELECT 'X' INTO temp FROM oe_order_lines_all WHERE line_id=p_line_id;
4062 x_exists_flag :='Y';
4063 COMMIT;
4064 EXCEPTION
4065 WHEN OTHERS THEN
4066 x_exists_flag :='N';
4067 COMMIT;
4068 END;
4069
4070
4071 END OE_Acknowledgment_Pub;
[Home] [Help]