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