[Home] [Help]
PACKAGE BODY: APPS.OE_DEALS_UTIL
Source
1 PACKAGE BODY OE_DEALS_UTIL as
2 /* $Header: OEXUDLSB.pls 120.10.12010000.2 2008/10/30 14:03:30 sgoli ship $ */
3
4 FUNCTION get_notified_activity(p_item_key varchar2 , p_item_type varchar2)
5 RETURN varchar2 IS
6 l_activity varchar2(30);
7 BEGIN
8
9 SELECT WPA.activity_name INTO l_activity
10 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
11 , WF_PROCESS_ACTIVITIES WPA
12 WHERE WIAS.item_type = p_item_type
13 AND WIAS.item_key = p_item_key
14 AND WIAS.activity_status = 'NOTIFIED'
15 AND WPA.instance_id = WIAS.process_activity;
16
17 return l_activity;
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 l_activity := NULL;
21 END;
22
23
24 FUNCTION IS_WF_Activity(p_header_id IN NUMBER)
25 RETURN BOOLEAN IS
26 l_root_activity VARCHAR2(30);
27 l_activity_present BOOLEAN;
28 l_item_type VARCHAR2(8);
29 l_TRANSACTION_PHASE_CODE varchar2(1);
30 BEGIN
31
32 select TRANSACTION_PHASE_CODE into l_TRANSACTION_PHASE_CODE FROM
33 OE_ORDER_HEADERS_ALL where header_id = p_header_id;
34
35 IF NVL(l_TRANSACTION_PHASE_CODE,'F') = 'F' THEN
36 l_item_type := 'OEOH';
37 ELSE
38 l_item_type := 'OENH';
39 END IF;
40
41 select ROOT_ACTIVITY INTO l_root_activity from wf_items where
42 item_type=l_item_type and item_key=to_char(p_header_id);
43
44 l_activity_present := OE_VALIDATE_WF.HAS_ACTIVITY
45 ( P_process => l_root_activity
46 , P_process_item_type => l_item_type
47 , P_activity => 'SUBMITTED_DEAL_WB'
48 , P_activity_item_type => OE_GLOBALS.G_WFI_HDR
49 );
50 RETURN l_activity_present;
51 EXCEPTION
52 WHEN OTHERS THEN
53 RETURN FALSE;
54 END;
55
56 Function Validate_config (p_header_id IN NUMBER)
57 RETURN boolean IS
58
59 p_deleted_options_tbl OE_Order_PUB.request_tbl_type;
60 p_updated_options_tbl OE_Order_PUB.request_tbl_type;
61 l_return_status VARCHAR2(1);
62 l_valid_config VARCHAR2(10);
63 l_complete_config VARCHAR2(10);
64 l_top_model_line_id NUMBER;
65 l_continue BOOLEAN := TRUE;
66 l_Config_header_id NUMBER;
67 l_Config_rev_nbr NUMBER;
68 l_Configuration_id NUMBER;
69
70
71 cursor config_lines is select line_id, Config_header_id, Config_rev_nbr, Configuration_id
72 FROM OE_ORDER_LINES_ALL where header_id = p_header_id AND
73 line_id= top_model_line_id and open_flag='Y' and nvl(cancelled_flag,'N') ='N';
74
75 BEGIN
76 l_return_status := FND_API.G_RET_STS_SUCCESS;
77 l_valid_config := 'TRUE';
78 l_complete_config := 'TRUE';
79
80
81 OPEN config_lines;
82 LOOP
83 FETCH config_lines into l_top_model_line_id, l_Config_header_id, l_Config_rev_nbr, l_Configuration_id ;
84 EXIT WHEN config_lines%NOTFOUND OR NOT l_continue;
85
86 -- if the Config is NOT selected-- DONT progress, also DONT call validation
87 IF(l_Config_header_id is NULL AND l_Config_rev_nbr is NULL AND l_Configuration_id is NULL ) THEN
88 l_continue := FALSE;
89 ELSE
90
91 oe_config_util.Validate_Configuration
92 (p_model_line_id => l_top_model_line_id,
93 p_deleted_options_tbl => p_deleted_options_tbl,
94 p_updated_options_tbl => p_updated_options_tbl,
95 p_validate_flag => 'Y',
96 p_complete_flag => 'N',
97 x_valid_config => l_valid_config,
98 x_complete_config => l_complete_config,
99 x_return_status => l_return_status);
100
101 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
102 IF (l_valid_config = 'TRUE' AND l_complete_config = 'TRUE' ) THEN
103 l_continue := TRUE;
104 ELSE
105 l_continue := FALSE;
106 END IF;
107 ELSE
108 l_continue := FALSE;
109 END IF;
110 END IF;
111 END LOOP;
112
113 CLOSE config_lines;
114 return l_continue;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 IF config_lines%ISOPEN THEN
119 CLOSE config_lines;
120 END IF;
121 l_continue := FALSE;
122 return l_continue;
123 END;
124
125
126 --Bug 6870738 STARTS
127 FUNCTION HAS_SAVED_REQUEST( p_header_id IN NUMBER
128 , p_instance_id IN NUMBER
129 )
130 RETURN VARCHAR2
131 IS
132 l_db_link VARCHAR2(240);
133 l_non_sim_deal_exists VARCHAR2(1) := 'N';--BOOLEAN := FALSE;
134 l_package VARCHAR2(30) := 'QPR_PRICE_NEGOTIATION_PUB';
135 l_function VARCHAR2(30) := 'HAS_SAVED_REQUESTS';
136 l_quote_origin NUMBER := 660;
137 l_dynamicSqlString VARCHAR2(2000);
138 BEGIN
139
140 l_db_link := FND_PROFILE.VALUE('QPR_PN_DBLINK') ;
141
142 IF l_db_link IS NOT NULL
143 THEN
144 l_db_link := '@' || l_db_link;
145 END IF;
146
147
148 l_dynamicSqlString := 'begin :l_non_sim_deal_exists := ';
149 l_dynamicSqlString := l_dynamicSqlString || l_package ||'.';
150 l_dynamicSqlString := l_dynamicSqlString || l_function || l_db_link ;
151 l_dynamicSqlString := l_dynamicSqlString || '(';
152 l_dynamicSqlString := l_dynamicSqlString || ':p_qoute_origin,';
153 l_dynamicSqlString := l_dynamicSqlString || ':p_qoute_header_id,';
154 l_dynamicSqlString := l_dynamicSqlString || ':p_instance_id); ';
155 l_dynamicSqlString := l_dynamicSqlString || ' end;';
156
157 EXECUTE IMMEDIATE l_dynamicSqlString
158 USING OUT l_non_sim_deal_exists,
159 IN l_quote_origin,
160 IN p_header_id,
161 IN p_instance_id;
162
163 RETURN l_non_sim_deal_exists;
164 EXCEPTION
165 WHEN OTHERS
166 THEN
167 l_non_sim_deal_exists := 'E';
168 RETURN l_non_sim_deal_exists;
169 END;
170 --Bug 6870738 ends
171
172 PROCEDURE CALL_DEALS_API(
173 p_header_id in NUMBER,
174 p_updatable_flag IN varchar2,
175 x_redirect_function out nocopy varchar2,
176 x_is_deal_compliant out nocopy varchar2,
177 x_rules_desc out nocopy varchar2,
178 x_return_status out nocopy varchar2,
179 x_msg_data out nocopy varchar2)
180
181 IS
182 l_package VARCHAR2(30) := 'QPR_PRICE_NEGOTIATION_PUB';
183 l_procedure VARCHAR2(30) := 'INITIATE_DEAL';
184 l_instance_id NUMBER;
185 l_db_link varchar2(240);
186 l_dynamicSqlString VARCHAR2(2000);
187 l_quote_origin NUMBER := 660;
188 --
189 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
190
191 BEGIN
192
193 l_instance_id := FND_PROFILE.VALUE('QPR_CURRENT_INSTANCE') ;
194 --If Instance ID is NULL, QPR API will fail so DONT call.
195 IF l_instance_id is NULL THEN
196 FND_MESSAGE.SET_NAME('ONT','OE_PROFILE_INCORRECT');
197 FND_MESSAGE.SET_TOKEN('PROFILE_NAME', 'QPR_CURRENT_INSTANCE');
198 OE_MSG_PUB.ADD;
199 x_return_status :='E';
200 x_is_deal_compliant := 'N';
201 ELSE
202
203 IF l_debug_level > 0 THEN
204 oe_debug_pub.add( 'CALL DEAL API' || l_procedure ) ;
205 END IF;
206 l_db_link := FND_PROFILE.VALUE('QPR_PN_DBLINK') ;
207
208 IF l_db_link is NOT NULL THEN
209 l_db_link := '@' || l_db_link;
210 END IF;
211
212
213 l_dynamicSqlString := ' begin ';
214 l_dynamicSqlString := l_dynamicSqlString || l_package ||'.';
215 l_dynamicSqlString := l_dynamicSqlString || l_procedure || l_db_link ;
216 l_dynamicSqlString := l_dynamicSqlString || '( ';
217 l_dynamicSqlString := l_dynamicSqlString || ':source_id, ';
218 l_dynamicSqlString := l_dynamicSqlString || ':source_ref_id,';
219 l_dynamicSqlString := l_dynamicSqlString || ':instance_id, ';
220 l_dynamicSqlString := l_dynamicSqlString || ':updatable, ';
221 -- OUT Parameters
222 l_dynamicSqlString := l_dynamicSqlString || ':redirect_function, ';
223 l_dynamicSqlString := l_dynamicSqlString || ':p_is_deal_compliant, ';
224 l_dynamicSqlString := l_dynamicSqlString || ':p_rules_desc, ';
225 l_dynamicSqlString := l_dynamicSqlString || ':x_return_status , ';
226 l_dynamicSqlString := l_dynamicSqlString || ':x_mesg_data ); ';
227 l_dynamicSqlString := l_dynamicSqlString || ' end; ';
228
229 EXECUTE IMMEDIATE l_dynamicSqlString USING
230 IN l_quote_origin,
231 IN p_header_id,
232 IN l_instance_id,
233 IN p_updatable_flag,
234 OUT x_redirect_function,
235 OUT x_is_deal_compliant,
236 OUT x_rules_desc,
237 OUT x_return_status,
238 OUT x_msg_data;
239 END IF;
240 IF l_debug_level > 0 THEN
241 oe_debug_pub.add( 'RETURN STATUS FROM l_is_deal_compliant: '||x_is_deal_compliant ) ;
242 oe_debug_pub.add( 'RETURN STATUS FROM l_deal_return_stat: '||x_return_status ) ;
243 oe_debug_pub.add( 'RETURN STATUS FROM QUERY: '||l_dynamicSqlString ) ;
244 oe_debug_pub.add( 'RETURN STATUS FROM x_msg_data: '|| x_msg_data ) ;
245 END IF;
246
247
248 EXCEPTION
249 when others then
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add( 'Raise Error in CALL_DEALs_API' ) ;
252 END IF;
253 x_return_status :='E';
254 x_is_deal_compliant := 'N';
255 END CALL_DEALS_API;
256
257
258 /*
259 NAME- COMPLIANCE_CHECK
260 DESC- This procedure is called from the WF activity COMPLIANCE_CHECK
261 Create the Deal.
262 If Deal is Approved, complete with Result=Y, Update Order status to PRICING APPROVED
263 If Deal is NOT Approved OR NOT created, complete with Result=N, Update Order status to PENDING PRICING APPROVAL
264 */
265
266 PROCEDURE COMPLIANCE_CHECK(
267 itemtype in varchar2,
268 itemkey in varchar2,
269 actid in number,
270 funcmode in varchar2,
271 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
272 IS
273 l_header_id NUMBER;
274 L_ORDER_NUMBER NUMBER;
275 L_QUOTE_NUMBER NUMBER;
276 L_VERSION_NUMBER NUMBER;
277 L_ORDER_TYPE_ID NUMBER;
278 L_ORG_ID NUMBER;
279 l_response_id NUMBER;
280 l_is_deal_compliant VARCHAR2(1);
281 l_order_type_name VARCHAR2(30);
282 l_redirect_function VARCHAR2(240);
283 l_deal_return_stat VARCHAR2(1);
284 l_deal_msg_data VARCHAR2(2000);
285
286
287 x_errbuf VARCHAR2(2000);
288 l_rules_desc VARCHAR2(240);
289 l_instance_id NUMBER;
290
291 l_dynamicSqlString VARCHAR2(2000);
292 l_updateable VARCHAR2(1) := 'N';
293
294 --Bug 6870738
295 l_non_sim_deal_exists VARCHAR2(1) := 'N';
296 --
297 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
298 BEGIN
299 if (funcmode = 'RUN') then
300
301 l_header_id := to_number(itemkey);
302 OE_STANDARD_WF.Set_Msg_Context(actid);
303 select
304 ORDER_NUMBER , QUOTE_NUMBER, VERSION_NUMBER, ORDER_TYPE_ID , ORG_ID
305 INTO
306 L_ORDER_NUMBER , L_QUOTE_NUMBER, L_VERSION_NUMBER, L_ORDER_TYPE_ID , L_ORG_ID
307 FROM
308 oe_order_headers_all where header_id= l_header_id;
309
310 L_ORDER_NUMBER := nvl(L_ORDER_NUMBER, L_QUOTE_NUMBER);
311
312 l_instance_id := FND_PROFILE.VALUE('QPR_CURRENT_INSTANCE') ;
313 --If Instance ID is NULL, QPR API will fail so DONT call.
314 IF l_instance_id is NULL THEN
315 FND_MESSAGE.SET_NAME('ONT','OE_PROFILE_INCORRECT');
316 FND_MESSAGE.SET_TOKEN('PROFILE_NAME', 'QPR_CURRENT_INSTANCE');
317 OE_MSG_PUB.ADD;
318 l_deal_return_stat :='E';
319 ELSE
320
321 --Bug 6870738 starts
322 /*
323 Checking if a deal exists in NON SIMULATE mode.
324 In such case should not continue compliance check
325 */
326 l_non_sim_deal_exists := HAS_SAVED_REQUEST(l_header_id, l_instance_id);
327
328 IF l_non_sim_deal_exists = 'Y'
329 THEN
330 resultout := 'COMPLETE:N';
331 OE_STANDARD_WF.Save_Messages;
332 OE_STANDARD_WF.Clear_Msg_Context;
333 FND_MESSAGE.SET_NAME('ONT', 'OE_COMPLIANCE_ERROR_DEAL_EXIST');
334 OE_MSG_PUB.ADD;
335
336 RETURN;
337 END IF;
338 --Bug 6870738 ends
339
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add( 'CALL DEAL API' ) ;
342 END IF;
343
344 CALL_DEALS_API(
345 p_header_id => l_header_id,
346 p_updatable_flag => 'Y',
347 x_redirect_function => l_redirect_function,
348 x_is_deal_compliant => l_is_deal_compliant,
349 x_rules_desc => l_rules_desc,
350 x_return_status => l_deal_return_stat,
351 x_msg_data => l_deal_msg_data);
352
353 END IF;
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'RETURN STATUS FROM l_is_deal_compliant: '||l_is_deal_compliant ) ;
356 oe_debug_pub.add( 'RETURN STATUS FROM l_response_id: '||l_response_id ) ;
357 oe_debug_pub.add( 'RETURN STATUS FROM l_deal_return_stat: '||l_deal_return_stat ) ;
358 oe_debug_pub.add( 'RETURN STATUS FROM QUERY: '||l_dynamicSqlString ) ;
359 END IF;
360
361 IF nvl(l_deal_return_stat,'S') = 'E' OR nvl(l_is_deal_compliant,'N') = 'N' THEN
362 resultout := 'COMPLETE:N';
363 OE_STANDARD_WF.Save_Messages;
364 OE_STANDARD_WF.Clear_Msg_Context;
365 FND_MESSAGE.SET_NAME('ONT', 'OE_ORDER_COMPLIANCE_FAILED');
366 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', L_ORDER_NUMBER);
367 OE_MSG_PUB.ADD;
368 IF (l_rules_desc is NOT NULL) THEN
369 FND_MESSAGE.SET_NAME('ONT', 'OE_WF_EXCEPTION');
370 FND_MESSAGE.SET_TOKEN('EXCEPTION', 'Rules Violated are: ' || l_rules_desc);
371 OE_MSG_PUB.ADD;
372 END IF;
373 return;
374 ELSE
375 UPDATE oe_order_headers_all SET
376 flow_status_code = 'PRICING_APPROVED'
377 WHERE header_id = l_header_id;
378
379 --Bug 7322917
380 --This is temporary. We need to update the flag using process order API.
381 --Even the above update statement has to be included in the call.
382 UPDATE OE_ORDER_LINES_ALL
383 SET CALCULATE_PRICE_FLAG = 'P'
384 WHERE header_id = l_header_id
385 AND open_flag='Y'
386 AND cancelled_flag='N';
387
388 resultout := 'COMPLETE:Y';
389 OE_STANDARD_WF.Save_Messages;
390 OE_STANDARD_WF.Clear_Msg_Context;
391 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_COMPLIANCE_PASSED');
392 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', L_ORDER_NUMBER);
393 OE_MSG_PUB.ADD;
394 return;
395 END IF;
396 END IF; -- End for 'RUN' mode
397
398 IF (funcmode = 'CANCEL') then
399 resultout := 'COMPLETE';
400 return;
401 END IF;
402 EXCEPTION
403 when others then
404 wf_core.context('OE_DEALS_UTIL', 'COMPLIANCE_CHECK',
405 itemtype, itemkey, to_char(actid), funcmode);
406 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
407 p_itemtype => itemtype,
408 p_itemkey => itemkey);
409 OE_STANDARD_WF.Save_Messages;
410 OE_STANDARD_WF.Clear_Msg_Context;
411 raise;
412 END COMPLIANCE_CHECK;
413
414
415
416
417
418
419
420
421
422 /*
423 NAME- Complete_Compliance_Eligible
424 DESC- This procedure is called when user selects Action -CHECK_COMPLIANCE (p_accept = Y)
425 OR Action-INVOKE_DEAL_WB (p_accept=N)
426 Check if the WF is eligible for Pricing Approval, If NOT, throw error msg.
427 If p-accept=y
428 just complete the WF activity-Pricing eligible with result =Y
429 The next WF activty COMPLIANCE_CHECK will create the DEAL.
430 If p-accept=N
431 Create the Deal
432 Update the Order Status to PENDING_PRICING_APPROVAL ( NO longer DEAL_SUBMITTED)
433 complete the WF activity-Pricing eligible with Result=N
434 */
435
436 PROCEDURE Complete_Compliance_Eligible
437 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
438 , p_header_id IN NUMBER
439 , p_accept IN VARCHAR2
440 , p_item_type IN VARCHAR2
441 , x_return_status OUT NOCOPY VARCHAR2
442 , x_msg_count OUT NOCOPY NUMBER
443 , x_msg_data OUT NOCOPY VARCHAR2
444 ) IS
445
446 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_Compliance_eligible';
447 l_api_version_number CONSTANT NUMBER := 1.0;
448 l_itemkey VARCHAR2(30);
449 l_Compliance_eligible VARCHAR2(1);
450 l_Compliance_errored_flag VARCHAR2(1);
451 l_order_source_id NUMBER;
452 l_orig_sys_document_ref VARCHAR2(50);
453 l_change_sequence VARCHAR2(50);
454 l_source_document_type_id NUMBER;
455 l_source_document_id NUMBER;
456
457 L_ORDER_NUMBER NUMBER;
458 l_quote_number NUMBER;
459 L_VERSION_NUMBER NUMBER;
460 L_ORDER_TYPE_ID NUMBER;
461 L_ORG_ID NUMBER;
462 l_is_deal_compliant VARCHAR2(1);
463 l_order_type_name VARCHAR2(30);
464 x_errbuf VARCHAR2(2000);
465 x_retcode NUMBER := 0;
466 l_rules_desc VARCHAR2(240);
467 l_instance_id NUMBER;
468
469 l_quote_origin NUMBER := 660;
470 l_dynamicSqlString VARCHAR2(2000);
471 l_package VARCHAR2(30) := 'QPR_PRICE_NEGOTIATION_PUB';
472 l_procedure VARCHAR2(30) := 'CREATE_PN_REQUEST';
473 l_db_link VARCHAR2(240);
474 l_simulation VARCHAR2(1) := 'N'; --For Submit to deals, simulation should be N
475 l_result varchar2(10);
476
477 CURSOR Compliance_eligible IS
478 SELECT 'Y'
479 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
480 , WF_PROCESS_ACTIVITIES WPA
481 WHERE WIAS.item_type = p_item_type
482 AND WIAS.item_key = l_itemkey
483 AND WIAS.activity_status = 'NOTIFIED'
484 AND WPA.activity_name = 'PRICING_APPROVAL_ELIGIBLE'
485 AND WPA.instance_id = WIAS.process_activity;
486
487 CURSOR Compliance_errored IS
488 SELECT 'Y'
489 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
490 , WF_PROCESS_ACTIVITIES WPA
491 WHERE WIAS.item_type = p_item_type
492 AND WIAS.item_key = l_itemkey
493 AND WIAS.activity_status = 'ERROR'
494 AND WPA.activity_name = 'PRICING_APPROVAL_ELIGIBLE'
495 AND WPA.instance_id = WIAS.process_activity;
496
497 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
498
499 BEGIN
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add( 'ENTER OE_DEALS_UTIL.COMPLETE_COMPLIANCE_ELIGIBLE' , 1 ) ;
502 END IF;
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504 -- Initialize message list.
505 IF FND_API.to_Boolean(p_init_msg_list) THEN
506 OE_MSG_PUB.initialize;
507 END IF;
508
509 SELECT order_source_id, orig_sys_document_ref, change_sequence, source_document_type_id, source_document_id,
510 ORDER_NUMBER , quote_number, VERSION_NUMBER, ORDER_TYPE_ID , ORG_ID
511 INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence, l_source_document_type_id, l_source_document_id,
512 L_ORDER_NUMBER , l_quote_number, L_VERSION_NUMBER, L_ORDER_TYPE_ID , L_ORG_ID
513 FROM OE_ORDER_HEADERS_ALL
514 WHERE HEADER_ID = p_header_id;
515
516 L_ORDER_NUMBER := nvl(L_ORDER_NUMBER, L_QUOTE_NUMBER); --For Quote Order No is NULL
517
518 OE_MSG_PUB.set_msg_context(
519 p_entity_code => 'HEADER'
520 ,p_entity_id => p_header_id
521 ,p_header_id => p_header_id
522 ,p_line_id => null
523 ,p_order_source_id => l_order_source_id
524 ,p_orig_sys_document_ref => l_orig_sys_document_ref
525 ,p_orig_sys_document_line_ref => null
526 ,p_change_sequence => l_change_sequence
527 ,p_source_document_type_id => l_source_document_type_id
528 ,p_source_document_id => l_source_document_id
529 ,p_source_document_line_id => null );
530
531 l_itemkey := to_char(p_header_id);
532
533 OPEN Compliance_eligible;
534 FETCH Compliance_eligible INTO l_Compliance_eligible;
535
536 IF (Compliance_eligible%NOTFOUND) THEN
537 IF l_debug_level > 0 THEN
538 oe_debug_pub.add( 'Compliance Check NOT ELIGIBLE' ) ;
539 END IF;
540 OPEN Compliance_errored;
541 FETCH Compliance_errored INTO l_Compliance_errored_flag;
542 IF (Compliance_errored%FOUND) THEN
543 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_COMP_CHECK_ERRORED');
544 OE_MSG_PUB.ADD;
545 RAISE FND_API.G_EXC_ERROR;
546 ELSE
547 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_COMP_NOT_ELIGIBLE');
548 OE_MSG_PUB.ADD;
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551 CLOSE Compliance_errored;
552 END IF;
553
554 CLOSE Compliance_eligible;
555
556 OE_ORDER_UTIL.Lock_Order_Object
557 (p_header_id => p_header_id
558 ,x_return_status => x_return_status
559 );
560 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
561 RAISE FND_API.G_EXC_ERROR;
562 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564 END IF;
565
566 OE_Standard_WF.OEOH_SELECTOR
567 (p_itemtype => p_item_type
568 ,p_itemkey => l_itemkey
569 ,p_actid => 12345
570 ,p_funcmode => 'SET_CTX'
571 ,p_result => l_result
572 );
573
574 WF_ENGINE.CompleteActivityInternalName
575 ( itemtype => p_item_type
576 , itemkey => l_itemkey
577 , activity => 'PRICING_APPROVAL_ELIGIBLE'
578 , result => p_accept
579 );
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add( 'AFTER CALLING WF_ENGINE' ) ;
582 END IF;
583
584 OE_MSG_PUB.set_msg_context(
585 p_entity_code => 'HEADER'
586 ,p_entity_id => p_header_id
587 ,p_header_id => p_header_id
588 ,p_line_id => null
589 ,p_order_source_id => l_order_source_id
590 ,p_orig_sys_document_ref => l_orig_sys_document_ref
591 ,p_orig_sys_document_line_ref => null
592 ,p_change_sequence => l_change_sequence
593 ,p_source_document_type_id => l_source_document_type_id
594 ,p_source_document_id => l_source_document_id
595 ,p_source_document_line_id => null );
596
597
598 OE_MSG_PUB.Count_And_Get
599 ( p_count => x_msg_count
600 , p_data => x_msg_data
601 );
602
603 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
604
605 IF l_debug_level > 0 THEN
606 oe_debug_pub.add( 'EXIT OE_DEALS_UTIL.COMPLETE_Compliance_eligible' , 1 ) ;
607 END IF;
608
609 EXCEPTION
610 WHEN FND_API.G_EXC_ERROR THEN
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add( 'EXC ERROR OE_DEALS_UTIL.COMPLETE_Compliance_eligible' , 1 ) ;
613 END IF;
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 IF (Compliance_eligible%ISOPEN) THEN
616 CLOSE Compliance_eligible;
617 END IF;
618 IF (Compliance_errored%ISOPEN) THEN
619 CLOSE Compliance_errored;
620 END IF;
621 OE_MSG_PUB.Count_And_Get
622 ( p_count => x_msg_count
623 , p_data => x_msg_data
624 );
625 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
626
627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
628 IF l_debug_level > 0 THEN
629 oe_debug_pub.add( 'EXC UNEXPECTED ERROR OE_DEALS_UTIL.COMPLETE_Compliance_eligible' , 1 ) ;
630 END IF;
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
632 IF (Compliance_eligible%ISOPEN) THEN
633 CLOSE Compliance_eligible;
634 END IF;
635 IF (Compliance_errored%ISOPEN) THEN
636 CLOSE Compliance_errored;
637 END IF;
638 OE_MSG_PUB.Count_And_Get
639 ( p_count => x_msg_count
640 , p_data => x_msg_data
641 );
642 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
643
644 WHEN OTHERS THEN
645 IF l_debug_level > 0 THEN
646 oe_debug_pub.add( 'OTHER ERROR OE_DEALS_UTIL.COMPLETE_Compliance_eligible' , 1 ) ;
647 END IF;
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 IF (Compliance_eligible%ISOPEN) THEN
650 CLOSE Compliance_eligible;
651 END IF;
652 IF (Compliance_errored%ISOPEN) THEN
653 CLOSE Compliance_errored;
654 END IF;
655 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
656 THEN
657 OE_MSG_PUB.Add_Exc_Msg
658 ( 'OE_DEALS_UTIL'
659 , 'Complete_Compliance_Eligible'
660 );
661 END IF;
662 OE_MSG_PUB.Count_And_Get
663 ( p_count => x_msg_count
664 , p_data => x_msg_data
665 );
666 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
667 END Complete_Compliance_Eligible;
668
669
670 --DEALS CALLING
671 Procedure Update_OM_with_deal(
672 source_id in number,
673 source_ref_id in number,
674 event in varchar2,
675 x_return_status out NOCOPY varchar2,
676 x_message_name out NOCOPY varchar2)
677
678 IS
679 l_header_id NUMBER;
680 l_return_status VARCHAR2(30);
681 l_msg_count NUMBER;
682 l_msg_data VARCHAR2(2000);
683 l_item_type VARCHAR2(8);
684 l_TRANSACTION_PHASE_CODE varchar2(1);
685 l_status varchar2(30);
686 l_new_status varchar2(30);
687 l_init_msg_list VARCHAR2(30) ;
688 l_result varchar2(10);
689 l_notified_activity varchar2(30);
690 --l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
691 l_debug_level CONSTANT NUMBER := 5;
692
693 l_file_val VARCHAR2(100);
694 BEGIN
695 l_header_id := source_ref_id;
696
697 --Comment this code to stop debugging
698 oe_debug_pub.debug_on;
699 oe_debug_pub.initialize;
700 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
701 oe_Debug_pub.setdebuglevel(5);
702 oe_debug_pub.add( 'deals called OM with-event' || EVENT || '-Header-' || l_header_id) ;
703
704 select TRANSACTION_PHASE_CODE , flow_status_code into l_TRANSACTION_PHASE_CODE , l_status FROM
705 OE_ORDER_HEADERS_ALL where header_id = l_header_id;
706
707 IF NVL(l_TRANSACTION_PHASE_CODE,'F') = 'F' THEN
708 l_item_type := 'OEOH';
709 ELSE
710 l_item_type := 'OENH';
711 END IF;
712
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add( 'Deals called OM with-event' || EVENT || '-Header-' || l_header_id) ;
715 END IF;
716
717 OE_ORDER_UTIL.Lock_Order_Object
718 (p_header_id => l_header_id
719 ,x_return_status => l_return_status
720 );
721 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
722 x_message_name := 'OE_LOCK_ROW_ALREADY_LOCKED';
723 RAISE FND_API.G_EXC_ERROR;
724 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
725 x_message_name := 'OE_LOCK_ROW_ALREADY_LOCKED';
726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727 END IF;
728
729 OE_STANDARD_WF.OEOH_SELECTOR(
730 p_itemtype => l_item_type,
731 p_itemkey => to_char(l_header_id),
732 p_actid => 12345,
733 p_funcmode => 'SET_CTX',
734 p_result => l_result);
735
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737 x_message_name := NULL;
738
739 --No acionable when deal is created
740 IF (event = 'CREATED') then
741 NULL;
742 END IF;
743
744 --when deal is submitted take WF to route 2, change status to Pending Pricing Approval
745 IF (event = 'SUBMITTED') THEN
746
747 IF OE_DEALS_UTIL.Validate_Config(p_header_id =>l_header_id) then
748
749 OE_DEALS_UTIL.Complete_Compliance_Eligible
750 ( p_init_msg_list => l_init_msg_list
751 , p_header_id => l_header_id
752 , p_accept => 'N'
753 , p_item_type => l_item_type
754 , x_return_status => x_return_status
755 , x_msg_count => l_msg_count
756 , x_msg_data => l_msg_data);
757 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
758 UPDATE oe_order_headers_all SET
759 flow_status_code = 'PENDING_PRICING_APPROVAL'
760 WHERE header_id = l_header_id;
761 ELSE
762 x_return_status := FND_API.G_RET_STS_ERROR;
763 x_message_name := 'OE_ORDER_COMP_CHECK_ERRORED';
764 END IF;
765 ELSE
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 x_message_name := 'OE_DEAL_CONFIG_FAIL';
768 END IF;
769 END IF;
770
771
772 --when deal is Approved just change status to Approved in deals, No wf change
773 IF (event = 'APPROVED') THEN
774 UPDATE oe_order_headers_all SET
775 flow_status_code = 'DEAL_APPROVED'
776 WHERE header_id = l_header_id;
777 END IF;
778
779 --when deal is Accepted, change status to Pricing Approved, update order with deal,
780 -- Progress WF, there can be 2 cases--direct accept --Pricing eligible->Pricing Approved
781 --Submit n Approve n accept --Submit Deal WB->Pricing Approved
782 IF (event = 'ACCEPTED') THEN
783 IF OE_DEALS_UTIL.Validate_Config(p_header_id =>l_header_id) then
784
785 OE_DEALS_UTIL.Update_Order_with_Deal
786 ( p_header_id => l_header_id
787 , p_item_type => l_item_type
788 , x_return_status => x_return_status
789 , x_msg_count => l_msg_count
790 , x_msg_data => l_msg_data);
791 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 x_message_name := 'OE_DEAL_UPDATE_FAILED';
794 END IF;
795
796 ELSE
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 x_message_name := 'OE_DEAL_CONFIG_FAIL';
799 END IF;
800
801 END IF;
802
803 --when deal is CancElled, change status to entered/Draft, wf moves to pricing eligible
804 IF (event = 'CANCELED') THEN
805
806 if (l_status <> 'ENTERED') THEN
807 if(l_item_type='OEOH') then
808 l_new_status:= 'ENTERED';
809 else
810 l_new_status:= 'DRAFT';
811 end if;
812 UPDATE oe_order_headers_all SET
813 flow_status_code = l_new_status
814 WHERE header_id = l_header_id;
815 l_notified_activity := get_notified_activity(to_char(l_header_id) , l_item_type);
816 if( nvl(l_notified_activity,'!@#$@#$') = 'SUBMITTED_DEAL_WB') THEN
817 WF_ENGINE.CompleteActivityInternalName
818 ( itemtype => l_item_type
819 , itemkey => to_char(l_header_id)
820 , activity => l_notified_activity
821 , result => 'REJECTED'
822 );
823 END IF;
824 END IF;
825 END IF;
826
827 --Bug 7039864
828 --Deal management requires message text and not the code
829 IF x_message_name IS NOT NULL
830 THEN
831 FND_MESSAGE.SET_NAME('ONT',x_message_name);
832 x_message_name := FND_MESSAGE.GET;
833 END IF;
834 oe_debug_pub.add('x_message_name: '||x_message_name);
835
836 EXCEPTION
837 WHEN OTHERS THEN
838 x_return_status := FND_API.G_RET_STS_ERROR;
839 --Bug 7039864
840 IF x_message_name IS NOT NULL
841 THEN
842 FND_MESSAGE.SET_NAME('ONT',x_message_name);
843 x_message_name := FND_MESSAGE.GET;
844 END IF;
845 oe_debug_pub.add('x_message_name: '||x_message_name);
846 END;
847
848
849 /*
850 NAME- Call_Process_Order
851 DESC- Calls the Process Order API to Update the Order
852 Update the Flow status Code of the Header.
853 Query the view QPR_INT_DEAL_V to get the new values for all lines in the Header.
854 Build the Line adjustment Info using the Modifier value from the Profile.
855 Check if the Modifier is already applied to the Line.
856 If modifier is applied then Operation is Update Else operation is Create
857 Call Process Order API
858 */
859
860 PROCEDURE Call_Process_Order (
861 p_header_id IN NUMBER
862 ,x_return_status OUT NOCOPY varchar2
863 ) IS
864 x_msg_count NUMBER;
865 x_msg_data VARCHAR2(2000);
866
867 l_init_msg_list VARCHAR2(30) ;
868 l_line_tbl Oe_Order_Pub.Line_Tbl_Type;
869 l_line_adj_tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
870 x_action_request_tbl Oe_Order_Pub.Request_Tbl_Type;
871 l_api_version_number NUMBER := 1.0 ;
872 x_msg_details VARCHAR2(3000) ;
873 x_msg_summary VARCHAR2(3000) ;
874
875 l_header_rec Oe_Order_Pub.Header_Rec_Type;
876 x_header_rec Oe_Order_Pub.Header_Rec_Type;
877 x_header_val_rec Oe_Order_Pub.Header_Val_Rec_Type;
878 x_Header_Adj_tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
879 x_Header_Adj_val_tbl Oe_Order_Pub.Header_Adj_Val_Tbl_Type;
880 x_Header_price_Att_tbl Oe_Order_Pub.Header_Price_Att_Tbl_Type;
881 x_Header_Adj_Att_tbl Oe_Order_Pub.Header_Adj_Att_Tbl_Type;
882 x_Header_Adj_Assoc_tbl Oe_Order_Pub.Header_Adj_Assoc_Tbl_Type;
883 x_Header_Scredit_tbl Oe_Order_Pub.Header_Scredit_Tbl_Type;
884 x_Header_Scredit_val_tbl Oe_Order_Pub.Header_Scredit_Val_Tbl_Type;
885 x_line_tbl Oe_Order_Pub.Line_Tbl_Type;
886 x_line_val_tbl Oe_Order_Pub.Line_Val_Tbl_Type;
887 x_Line_Adj_tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
888 x_Line_Adj_val_tbl Oe_Order_Pub.Line_Adj_Val_Tbl_Type;
889 x_Line_price_Att_tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type;
890 x_Line_Adj_Att_tbl Oe_Order_Pub.Line_Adj_Att_Tbl_Type;
891 x_Line_Adj_Assoc_tbl Oe_Order_Pub.Line_Adj_Assoc_Tbl_Type;
892 x_Line_Scredit_tbl Oe_Order_Pub.Line_Scredit_Tbl_Type;
893 x_Line_Scredit_val_tbl Oe_Order_Pub.Line_Scredit_Val_Tbl_Type;
894 x_Lot_Serial_tbl Oe_Order_Pub.Lot_Serial_Tbl_Type;
895 x_Lot_Serial_val_tbl Oe_Order_Pub.Lot_Serial_Val_Tbl_Type;
896
897 l_uom_code varchar2(30);
898 l_currency_code varchar2(30);
899 l_ordered_qty NUMBER;
900 l_price NUMBER;
901 l_payment_term_id NUMBER;
902 l_ship_method_code varchar2(30);
903 l_list_line_id NUMBER;
904 l_list_line_profile varchar2(30);
905
906 l_list_header_id NUMBER;
907
908 l_sqlstmt varchar2(1000);
909 l_status varchar2(10) :='ACCEPT';
910 l_db_link varchar2(240);
911 l_continue varchar2(1);
912 i number :=0;
913 l_price_adj_id NUMBER ;
914 l_adjusted_amount NUMBER;
915 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
916 cursor lines_cursor is select line_id, unit_selling_price from oe_order_lines_all where header_id=p_header_id
917 and open_flag='Y' and cancelled_flag='N';
918
919 BEGIN
920
921 IF l_debug_level > 0 THEN
922 oe_debug_pub.add( 'call Process Order API ' , 1 ) ;
923 END IF;
924
925 x_return_status := FND_API.G_RET_STS_SUCCESS;
926 x_msg_count := NULL ;
927 x_msg_data := NULL ;
928
929 l_list_line_profile := FND_PROFILE.VALUE('QPR_DEAL_DIFF_MODIFIER') ;
930 l_list_line_id := to_number(l_list_line_profile);
931 l_db_link := FND_PROFILE.VALUE('QPR_PN_DBLINK') ;
932
933 IF l_db_link is NOT NULL THEN
934 l_db_link := '@' || l_db_link;
935 END IF;
936
937 IF l_list_line_id is NULL THEN
938 FND_MESSAGE.SET_NAME('ONT','OE_PROFILE_INCORRECT');
939 FND_MESSAGE.SET_TOKEN('PROFILE_NAME', 'QPR_DEAL_DIFF_MODIFIER');
940 OE_MSG_PUB.ADD;
941 IF l_debug_level > 0 THEN
942 oe_debug_pub.add( 'Profile is NULL ' , 1 ) ;
943 END IF;
944 RAISE FND_API.G_EXC_ERROR;
945 END IF;
946
947 IF l_debug_level > 0 THEN
948 oe_debug_pub.add( 'In call Process Order API-Modifier ' || l_list_line_id , 1 ) ;
949 END IF;
950
951 select list_header_id INTO l_list_header_id from qp_list_lines where list_line_id=l_list_line_id;
952 --Update the Header,
953 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
954 l_header_rec.header_id := p_header_id;
955 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
956 l_header_rec.flow_status_code := 'PRICING_APPROVED';
957 IF l_debug_level > 0 THEN
958 oe_debug_pub.add( 'In call Process Order API before Loop' , 1 ) ;
959 END IF;
960
961 for l_lines in lines_cursor LOOP
962 l_continue:='Y';
963 l_sqlstmt :=' SELECT UOM_CODE ,CURRENCY_CODE ,ORDERED_QTY ,PRICE ,PAYMENT_TERM_ID ,SHIP_METHOD_CODE ' ||
964 ' FROM QPR_INT_DEAL_V' || l_db_link ||
965 ' WHERE SOURCE = 660 AND STATUS= ' || '''ACCEPT''' ||
966 ' AND CHANGED = ' || '''Y''' ||
967 ' AND SOURCE_REF_HEADER_ID = :p_header_id ' ||
968 ' AND SOURCE_REF_LINE_ID = :l_line_id ' ;
969
970 IF l_debug_level > 0 THEN
971 oe_debug_pub.add( 'In call Process Order API before execute immediate' || l_lines.line_id , 1 ) ;
972 oe_debug_pub.add( 'In call Process Order API before execute immediate' || l_sqlstmt , 1 ) ;
973 oe_debug_pub.add( 'In call Process Order API before execute immediate' || p_header_id || '-' || l_status , 1 ) ;
974 END IF;
975
976 BEGIN
977 EXECUTE IMMEDIATE l_sqlstmt INTO
978 l_uom_code ,l_currency_code ,l_ordered_qty ,l_price ,l_payment_term_id ,l_ship_method_code
979 USING p_header_id, l_lines.line_id;
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 l_continue:='N';
983 oe_debug_pub.add( 'Exec immediate NO data found-Dont update this Line' , 1 ) ;
984 END;
985 IF l_debug_level > 0 THEN
986 oe_debug_pub.add( 'In call Process Order API execute immediate-' || l_continue, 1 ) ;
987 END IF;
988
989 IF (l_continue='Y') then
990 i := i+1;
991 --Order Line Information
992 l_line_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_REC;
993 l_line_tbl(i).header_id := p_header_id;
994 l_line_tbl(i).line_id := l_lines.line_id;
995 l_line_tbl(i).unit_selling_price := l_price;
996 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
997 l_line_tbl(i).calculate_price_flag := 'P';
998 l_line_tbl(i).shipping_method_code := l_ship_method_code;
999 l_line_tbl(i).order_quantity_uom := l_uom_code;
1000 l_line_tbl(i).ordered_quantity := l_ordered_qty;
1001 l_line_tbl(i).payment_term_id := l_payment_term_id;
1002
1003 BEGIN
1004 select price_adjustment_id, operand into l_price_adj_id, l_adjusted_amount from oe_price_adjustments where
1005 header_id=p_header_id AND
1006 line_id =l_lines.line_id AND
1007 list_line_id =l_list_line_id and list_header_id = l_list_header_id
1008 and applied_flag='Y' and rownum <2;
1009 EXCEPTION
1010 WHEN OTHERS THEN --Modifier is NOT APPLIED
1011 l_price_adj_id := NULL;
1012 END;
1013 IF l_debug_level > 0 THEN
1014 oe_debug_pub.add( 'In call Process Order API Adjustment' , 1 ) ;
1015 END IF;
1016
1017 --Order Line Adjustment Information
1018 l_line_adj_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_ADJ_REC;
1019 l_line_adj_tbl(i).line_index := i; --adjustment for the line above
1020 l_line_adj_tbl(i).list_header_id := l_list_header_id; --oe_price_adjustments
1021 l_line_adj_tbl(i).list_line_id := l_list_line_id;
1022 l_line_adj_tbl(i).arithmetic_operator := 'AMT';
1023 l_line_adj_tbl(i).change_reason_code := 'DEALS';
1024 l_line_adj_tbl(i).updated_flag := 'Y';
1025 l_line_adj_tbl(i).applied_flag := 'Y';
1026 IF l_price_adj_id is NULL THEN
1027 l_line_adj_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
1028 l_line_adj_tbl(i).operand := l_lines.unit_selling_price - l_price ;
1029 l_line_adj_tbl(i).adjusted_amount := l_lines.unit_selling_price - l_price ;
1030 ELSE
1031 l_line_adj_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
1032 l_line_adj_tbl(i).price_adjustment_id := l_price_adj_id;
1033 l_line_adj_tbl(i).operand := l_lines.unit_selling_price - l_price + l_adjusted_amount;
1034 l_line_adj_tbl(i).adjusted_amount := l_lines.unit_selling_price - l_price + l_adjusted_amount;
1035 END IF;
1036 ELSE
1037 --Bug 7322917
1038 i := i+1;
1039 --Order Line Information
1040 l_line_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_REC;
1041 l_line_tbl(i).header_id := p_header_id;
1042 l_line_tbl(i).line_id := l_lines.line_id;
1043 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
1044 l_line_tbl(i).calculate_price_flag := 'P';
1045 END IF;
1046 END LOOP;
1047 IF l_debug_level > 0 THEN
1048 oe_debug_pub.add( 'Status Before PO API call ' || l_line_tbl.count, 1 ) ;
1049 END IF;
1050
1051 Oe_Order_Pvt.Process_Order(
1052 p_api_version_number => '1.0'
1053 , p_init_msg_list => FND_API.G_FALSE
1054 , x_return_status => x_return_status
1055 , x_msg_count => x_msg_count
1056 , x_msg_data => x_msg_data
1057 --IN PARAMETERS
1058 , p_x_header_rec => l_header_rec
1059 , p_x_line_tbl => l_line_tbl
1060 , p_x_line_adj_tbl => l_line_adj_tbl
1061 , p_x_Header_Adj_tbl => x_Header_Adj_tbl
1062 , p_x_Header_price_Att_tbl => x_Header_price_Att_tbl
1063 , p_x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
1064 , p_x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
1065 , p_x_Header_Scredit_tbl => x_Header_Scredit_tbl
1066 , p_x_Line_price_Att_tbl => x_Line_price_Att_tbl
1067 , p_x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
1068 , p_x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
1069 , p_x_Line_Scredit_tbl => x_Line_Scredit_tbl
1070 , p_x_Lot_Serial_tbl => x_Lot_Serial_tbl
1071 , p_x_action_request_tbl => x_action_request_tbl
1072 );
1073
1074
1075 IF l_debug_level > 0 THEN
1076 oe_debug_pub.add( 'Status after PO API call from deals ' || x_return_status, 1 ) ;
1077 END IF;
1078 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1079 IF l_debug_level > 0 THEN
1080 oe_debug_pub.add( 'Raising Error', 1 ) ;
1081 END IF;
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN FND_API.G_EXC_ERROR THEN
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088
1089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1091
1092 FOR k IN 1 .. x_msg_count LOOP
1093 x_msg_data := oe_msg_pub.get( p_msg_index => k,p_encoded => 'F');
1094 END LOOP;
1095
1096 WHEN OTHERS THEN
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1098 IF l_debug_level > 0 THEN
1099 oe_debug_pub.add( 'Status other ' || sqlerrm, 1 ) ;
1100 END IF;
1101
1102 END Call_Process_Order;
1103
1104 /*
1105 NAME-Get_Deal_Info
1106 Desc - Calls the QPR API to get the Deal ID of the Deal created for an Order.
1107 The Deal_id and deal_status will be NULL if deal is NOT created
1108 The deal_status will be Y if deal is APPROVED
1109 The deal_status will be N if deal is created but NOT APPROVED
1110 */
1111
1112 PROCEDURE Get_Deal_Info
1113 ( p_header_id IN NUMBER
1114 , x_deal_status OUT NOCOPY VARCHAR2
1115 , x_deal_id OUT NOCOPY NUMBER
1116 ) IS
1117
1118 x_errbuf VARCHAR2(2000);
1119 x_retcode NUMBER;
1120
1121 l_quote_origin NUMBER := 660;
1122 l_dynamicSqlString VARCHAR2(2000);
1123 l_package VARCHAR2(30) := 'QPR_PRICE_NEGOTIATION_PUB';
1124 l_procedure VARCHAR2(30) := 'get_pn_approval_status';
1125 l_db_link varchar2(240);
1126 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1127
1128 BEGIN
1129
1130 IF l_debug_level > 0 THEN
1131 oe_debug_pub.add( 'ENTER OE_DEALS_UTIL.get_Deal' , 1 ) ;
1132 END IF;
1133 x_deal_id := NULL;
1134 x_deal_status := NULL;
1135
1136 l_db_link := FND_PROFILE.VALUE('QPR_PN_DBLINK') ;
1137
1138 IF l_db_link is NOT NULL THEN
1139 l_db_link := '@' || l_db_link;
1140 END IF;
1141
1142 l_dynamicSqlString := ' begin ';
1143 l_dynamicSqlString := l_dynamicSqlString || l_package ||'.';
1144 l_dynamicSqlString := l_dynamicSqlString || l_procedure || l_db_link ;
1145 l_dynamicSqlString := l_dynamicSqlString || '( ';
1146 l_dynamicSqlString := l_dynamicSqlString || ':errbuf, ';
1147 l_dynamicSqlString := l_dynamicSqlString || ':retcode, ';
1148 l_dynamicSqlString := l_dynamicSqlString || ':p_quote_origin, ';
1149 l_dynamicSqlString := l_dynamicSqlString || ':p_quote_header_id, ';
1150 l_dynamicSqlString := l_dynamicSqlString || ':o_deal_id, ';
1151 l_dynamicSqlString := l_dynamicSqlString || ':o_status );';
1152 l_dynamicSqlString := l_dynamicSqlString || ' end; ';
1153
1154 -- EXECUTE THE DYNAMIC SQL
1155 EXECUTE IMMEDIATE l_dynamicSqlString USING
1156 OUT x_errbuf,
1157 OUT x_retcode,
1158 IN l_quote_origin,
1159 IN p_header_id,
1160 OUT x_deal_id,
1161 OUT x_deal_status;
1162
1163 IF l_debug_level > 0 THEN
1164 oe_debug_pub.add( 'Exit OE_DEALS_UTIL.get_Deal QUERY-' || l_dynamicSqlString, 1 ) ;
1165 oe_debug_pub.add( 'Exit OE_DEALS_UTIL.get_Deal' || x_deal_id || x_deal_status, 1 ) ;
1166 END IF;
1167 EXCEPTION
1168 WHEN OTHERS THEN
1169 x_deal_id := NULL;
1170 x_deal_status := NULL;
1171 END Get_Deal_Info;
1172
1173
1174 /*
1175 NAME-Update_Order_with_Deal
1176 Desc -Update the Order with the Approved Deal.
1177 Get the deal Info.
1178 If deal is NOT create or NOT approved,
1179 show message
1180 If deal is approved,
1181 call Process Order to Update the Order.
1182 Progress the WF
1183 */
1184
1185 PROCEDURE Update_Order_with_Deal
1186 ( p_header_id IN NUMBER
1187 , p_item_type IN VARCHAR2
1188 , x_return_status OUT NOCOPY VARCHAR2
1189 , x_msg_count OUT NOCOPY NUMBER
1190 , x_msg_data OUT NOCOPY VARCHAR2
1191 ) IS
1192 l_order_source_id NUMBER;
1193 l_orig_sys_document_ref VARCHAR2(50);
1194 l_change_sequence VARCHAR2(50);
1195 l_source_document_type_id NUMBER;
1196 l_source_document_id NUMBER;
1197
1198 l_deal_id NUMBER;
1199 l_deal_status VARCHAR2(30);
1200 l_wf_activity VARCHAR2(30);
1201 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1202 l_wf_count NUMBER;
1203
1204 BEGIN
1205
1206 IF l_debug_level > 0 THEN
1207 oe_debug_pub.add( 'ENTER OE_DEALS_UTIL.Update_Order_with_Deal' , 1 ) ;
1208 END IF;
1209
1210 SELECT order_source_id, orig_sys_document_ref, change_sequence, source_document_type_id, source_document_id
1211 INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence, l_source_document_type_id, l_source_document_id
1212 FROM OE_ORDER_HEADERS_ALL
1213 WHERE HEADER_ID = p_header_id;
1214
1215 OE_MSG_PUB.set_msg_context(
1216 p_entity_code => 'HEADER'
1217 ,p_entity_id => p_header_id
1218 ,p_header_id => p_header_id
1219 ,p_line_id => null
1220 ,p_order_source_id => l_order_source_id
1221 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1222 ,p_orig_sys_document_line_ref => null
1223 ,p_change_sequence => l_change_sequence
1224 ,p_source_document_type_id => l_source_document_type_id
1225 ,p_source_document_id => l_source_document_id
1226 ,p_source_document_line_id => null );
1227
1228 l_deal_id := NULL;
1229 x_return_status := FND_API.G_RET_STS_SUCCESS;
1230
1231
1232 IF l_debug_level > 0 THEN
1233 oe_debug_pub.add( 'Deal Info - ' || l_deal_id || l_deal_status , 1 ) ;
1234 END IF;
1235
1236 -- Approved Deal, Call PO API update the Order
1237 Call_Process_Order (p_header_id => p_header_id
1238 ,x_return_status => x_return_status);
1239
1240 IF l_debug_level > 0 THEN
1241 oe_debug_pub.add( 'After PO API from Deal - ' || x_return_status , 1 ) ;
1242 END IF;
1243
1244 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1245
1246 --there can be 2 cases--Direct approve without submit-- so progress both activity
1247 --and submit-approve - progress only submit WF activity
1248 BEGIN
1249 select WPA.activity_name INTO l_wf_activity
1250 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
1251 , WF_PROCESS_ACTIVITIES WPA
1252 WHERE WIAS.item_type = p_item_type
1253 AND WIAS.item_key = to_char(p_header_id)
1254 AND WIAS.activity_status = 'NOTIFIED'
1255 AND WPA.activity_name in ( 'SUBMITTED_DEAL_WB', 'PRICING_APPROVAL_ELIGIBLE')
1256 AND WPA.instance_id = WIAS.process_activity;
1257
1258 EXCEPTION
1259 WHEN NO_DATA_FOUND THEN
1260 l_wf_count := 0;
1261 l_wf_activity := NULL;
1262 WHEN OTHERS THEN
1263 l_wf_count := 0;
1264 l_wf_activity := NULL;
1265
1266 END;
1267 IF l_debug_level > 0 THEN
1268 oe_debug_pub.add( 'After PO API from Deal - ' || l_wf_activity , 1 ) ;
1269 END IF;
1270
1271 if l_wf_activity is NULL then
1272 NULL ;
1273 ELSE
1274 if l_wf_activity = 'PRICING_APPROVAL_ELIGIBLE' then
1275 WF_ENGINE.CompleteActivityInternalName
1276 ( itemtype => p_item_type
1277 , itemkey => to_char(p_header_id)
1278 , activity => l_wf_activity
1279 , result => 'N'
1280 );
1281
1282 END IF;
1283 WF_ENGINE.CompleteActivityInternalName
1284 ( itemtype => p_item_type
1285 , itemkey => to_char(p_header_id)
1286 , activity => 'SUBMITTED_DEAL_WB'
1287 , result => 'APPROVED'
1288 );
1289
1290 IF l_debug_level > 0 THEN
1291 oe_debug_pub.add( 'After WF Engine call - ' || l_wf_count , 1 ) ;
1292 END IF;
1293
1294
1295 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_DEAL_UPDATED');
1296 OE_MSG_PUB.ADD;
1297 END IF;
1298 ELSE
1299 FND_MESSAGE.SET_NAME('ONT','OE_DEAL_UPDATE_FAILED');
1300 OE_MSG_PUB.ADD;
1301 RAISE FND_API.G_EXC_ERROR;
1302 END IF;
1303 OE_MSG_PUB.set_msg_context(
1304 p_entity_code => 'HEADER'
1305 ,p_entity_id => p_header_id
1306 ,p_header_id => p_header_id
1307 ,p_line_id => null
1308 ,p_order_source_id => l_order_source_id
1309 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1310 ,p_orig_sys_document_line_ref => null
1311 ,p_change_sequence => l_change_sequence
1312 ,p_source_document_type_id => l_source_document_type_id
1313 ,p_source_document_id => l_source_document_id
1314 ,p_source_document_line_id => null );
1315 OE_MSG_PUB.Count_And_Get
1316 ( p_count => x_msg_count
1317 , p_data => x_msg_data
1318 );
1319
1320 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1324 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1325 OE_MSG_PUB.Add_Exc_Msg
1326 ( 'OE_DEALS_UTIL'
1327 , 'Update_Order_with_Deal'
1328 );
1329 END IF;
1330 OE_MSG_PUB.Count_And_Get
1331 ( p_count => x_msg_count
1332 , p_data => x_msg_data
1333 );
1334 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
1335 END Update_Order_with_Deal;
1336
1337 END OE_DEALS_UTIL;