1 Package body OE_OTA_UTIL As
2 /* $Header: OEXUOTAB.pls 120.0 2005/06/01 01:01:32 appldev noship $ */
3
4 G_OTA_STATUS VARCHAR2(1) := FND_API.G_MISS_CHAR;
5
6 Function Get_Product_Status(p_application_id NUMBER)
7 RETURN VARCHAR2 IS
8 l_ret_val BOOLEAN;
9 l_status VARCHAR2(1);
10 l_industry VARCHAR2(1);
11 --
12 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
13 --
14 BEGIN
15
16 if (p_application_id = 810
17 AND G_OTA_STATUS = FND_API.G_MISS_CHAR)
18 then
19 IF l_debug_level > 0 THEN
20 oe_debug_pub.add( 'GET OTA PROD. STATUS' ) ;
21 END IF;
22
23 -- Make a call to fnd_installation.get function to check for the
24 -- installation status of the CRM products and return the status.
25
26 l_ret_val := fnd_installation.get(p_application_id,p_application_id
27 ,l_status,l_industry);
28 if p_application_id = 810 then
29 G_OTA_STATUS := l_status;
30 end if;
31
32 end if;
33
34 if p_application_id = 810 then
35 IF l_debug_level > 0 THEN
36 oe_debug_pub.add( 'OTA RET PROD. STATUS :'||G_OTA_STATUS ) ;
37 END IF;
38 return (G_OTA_STATUS);
39 end if;
40
41 END Get_Product_Status;
42
43 Procedure Notify_OTA
44 ( p_line_id IN NUMBER
45 , p_org_id IN NUMBER
46 , p_order_quantity_uom IN VARCHAR2
47 , p_daemon_type IN VARCHAR2
48 , x_return_status OUT NOCOPY VARCHAR2
49
50 )
51 IS
52
53 l_return_status VARCHAR2(1);
54 l_sql_stat VARCHAR2(3000);
55
56 --
57 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
58 --
59 BEGIN
60
61 IF l_debug_level > 0 THEN
62 oe_debug_pub.add( 'ENTERING NOTIFY_OTA API' ) ;
63 END IF;
64
65
66 /* The application id for Order Capture is 697 */
67
68 --IF Get_Product_Status(810) IN ('I','S') THEN
69
70 -- lkxu, for bug 1701377
71 IF OE_GLOBALS.G_OTA_INSTALLED IS NULL THEN
72 OE_GLOBALS.G_OTA_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(810);
73 END IF;
74
75 IF OE_GLOBALS.G_OTA_INSTALLED = 'Y' THEN
76
77 -- Call the OTA API
78 l_sql_stat := '
79 Begin
80 OTA_CANCEL_API.DELETE_CANCEL_LINE(
81 :p_line_id
82 , :p_org_id
83 , :p_uom
84 , :p_daemon_type
85 , :x_return_status);
86 END;';
87
88
89 EXECUTE IMMEDIATE l_sql_stat
90 USING IN p_line_id
91 , IN p_org_id
92 , IN p_order_quantity_uom
93 , IN p_daemon_type
94 , OUT l_return_status;
95
96
97 x_return_status := l_return_status;
98
99 IF l_debug_level > 0 THEN
100 oe_debug_pub.add( 'JPN: OTA RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
101 END IF;
102
103 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
104 IF l_debug_level > 0 THEN
105 oe_debug_pub.add( 'NOTIFY_OTA API - UNEXPECTED ERROR' ) ;
106 END IF;
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( 'EXITING NOTIFY_OTA API' ) ;
109 END IF;
110 /* OE_DEBUG_PUB.ADD('Notify OC error msg is: ' || substr(x_msg_data, 1,200)); */
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
113 IF l_debug_level > 0 THEN
114 oe_debug_pub.add( 'NOTIFY_OTA API - ERROR' ) ;
115 END IF;
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add( 'EXITING NOTIFY_OTA API' ) ;
118 END IF;
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121
122 END IF; -- API exists
123
124 EXCEPTION
125
126
127 WHEN FND_API.G_EXC_ERROR THEN
128
129 x_return_status := FND_API.G_RET_STS_ERROR;
130
131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
132
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134
135 WHEN OTHERS THEN
136
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138
139 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
140 THEN
141 OE_MSG_PUB.Add_Exc_Msg
142 ( G_PKG_NAME
143 , 'NOTIFY_OTA'
144 );
145 END IF;
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147
148 END Notify_OTA;
149
150
151 PROCEDURE Get_Enrollment_Status(p_line_id IN NUMBER
152 ,x_valid OUT NOCOPY VARCHAR2
153
154 ,x_return_status OUT NOCOPY VARCHAR2)
155
156 IS
157 l_sql_stat VARCHAR2(3000);
158 l_valid VARCHAR2(1);
159 l_return_status VARCHAR2(1);
160
161 --
162 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
163 --
164 BEGIN
165
166 IF l_debug_level > 0 THEN
167 oe_debug_pub.add( 'ENTERING GET OTA ENROLLMENT' , 1 ) ;
168 END IF;
169 -- IF Get_Product_Status(810) IN ('I','S') THEN
170
171 -- lkxu, for bug 1701377
172 IF OE_GLOBALS.G_OTA_INSTALLED IS NULL THEN
173 OE_GLOBALS.G_OTA_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(810);
174 END IF;
175
176 IF OE_GLOBALS.G_OTA_INSTALLED = 'Y' THEN
177
178 -- Call the OTA Enrollment status checking API
179
180 l_sql_stat := '
181 Begin
182 OTA_UTILITY.CHECK_ENROLLMENT(
183 :p_line_id
184 ,:x_valid
185 ,:x_return_status
186 );
187 END;';
188
189 EXECUTE IMMEDIATE l_sql_stat
190 USING IN p_line_id
191 , OUT l_valid
192
193 , OUT l_return_status;
194
195
196 x_return_status := l_return_status;
197 x_valid := l_valid;
198
199 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
200 IF l_debug_level > 0 THEN
201 oe_debug_pub.add( 'GET_ENROLLMENT_STATUS API - UNEXPECTED ERROR' ) ;
202 END IF;
203 IF l_debug_level > 0 THEN
204 oe_debug_pub.add( 'EXITING GET_ENROLLMENT_STATUS API' ) ;
205 END IF;
206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
207 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
208 IF l_debug_level > 0 THEN
209 oe_debug_pub.add( 'GET_ENROLLMENT_STATUS API - ERROR' ) ;
210 END IF;
211 IF l_debug_level > 0 THEN
212 oe_debug_pub.add( 'EXITING GET_ENROLLMENT_STATUS API' ) ;
213 END IF;
214 RAISE FND_API.G_EXC_ERROR;
215
216 END IF;
217
218 END IF;
219
220
221
222 End Get_Enrollment_Status;
223
224
225 PROCEDURE Get_Event_Status(p_line_id IN NUMBER
226 ,x_valid OUT NOCOPY VARCHAR2
227
228 ,x_return_status OUT NOCOPY VARCHAR2)
229
230 IS
231 l_sql_stat VARCHAR2(3000);
232 l_valid VARCHAR2(1);
233 l_return_status VARCHAR2(1);
234
235 --
236 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
237 --
238 BEGIN
239
240 IF l_debug_level > 0 THEN
241 oe_debug_pub.add( 'ENTERING GET OTA ENROLLMENT' , 1 ) ;
242 END IF;
243 -- IF Get_Product_Status(810) IN ('I','S') THEN
244
245 -- lkxu, for bug 1701377
246 IF OE_GLOBALS.G_OTA_INSTALLED IS NULL THEN
247 OE_GLOBALS.G_OTA_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(810);
248 END IF;
249
250 IF OE_GLOBALS.G_OTA_INSTALLED = 'Y' THEN
251
252 -- Call the OTA Event status checking API
253
254 l_sql_stat := '
255 Begin
256 OTA_UTILITY.CHECK_EVENT(
257 :p_line_id
258 ,:x_valid
259 ,:x_return_status
260 );
261 END;';
262
263 EXECUTE IMMEDIATE l_sql_stat
264 USING IN p_line_id
265 , OUT l_valid
266
267 , OUT l_return_status;
268
269
270 x_return_status := l_return_status;
271 x_valid := l_valid;
272
273 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
274 IF l_debug_level > 0 THEN
275 oe_debug_pub.add( 'GET_EVENT_STATUS API - UNEXPECTED ERROR' ) ;
276 END IF;
277 IF l_debug_level > 0 THEN
278 oe_debug_pub.add( 'EXITING GET_EVENT_STATUS API' ) ;
279 END IF;
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
282 IF l_debug_level > 0 THEN
283 oe_debug_pub.add( 'GET_EVENT_STATUS API - ERROR' ) ;
284 END IF;
285 IF l_debug_level > 0 THEN
286 oe_debug_pub.add( 'EXITING GET_EVENT_STATUS API' ) ;
287 END IF;
288 RAISE FND_API.G_EXC_ERROR;
289
290 END IF;
291
292 END IF;
293
294
295 End Get_Event_Status;
296
297
298 PROCEDURE Get_OTA_Description
299 (p_line_id IN NUMBER
300 ,p_uom IN VARCHAR2
301 ,x_description OUT NOCOPY VARCHAR2
302
303 ,x_course_end_date OUT NOCOPY DATE
304
305 ,x_return_status OUT NOCOPY VARCHAR2
306
307 )
308 IS
309
310 l_sql_stat VARCHAR2(3000);
311 l_description VARCHAR2(2000);
312
313 --
314 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
315 --
316 BEGIN
317
318 IF l_debug_level > 0 THEN
319 oe_debug_pub.add( 'ENTERING GET_OTA DESCRIPTION' , 1 ) ;
320 END IF;
321 -- IF Get_Product_Status(810) IN ('I','S') THEN
322
323 -- lkxu, for bug 1701377
324 IF OE_GLOBALS.G_OTA_INSTALLED IS NULL THEN
325 OE_GLOBALS.G_OTA_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(810);
326 END IF;
327
328 IF OE_GLOBALS.G_OTA_INSTALLED = 'Y' THEN
329
330 -- Call the OTA API to get the item description to be interfaced
331 -- with AR. The OTA API will also return the sourse end date that will
332 -- need to be interfaced with AR as GL date.
333
334 l_sql_stat := '
335 Begin
336 OTA_UTILITY.Get_Description(
337 :p_line_id
338 ,:p_uom
339 ,:x_description
340 ,:x_course_end_date
341 ,:x_return_status
342 );
343 END;';
344 EXECUTE IMMEDIATE l_sql_stat
345 USING IN p_line_id
346 , IN p_uom
347 , OUT l_description
348
349 , OUT x_course_end_date
350
351 , OUT x_return_status;
352
353
354 x_description := l_description;
355
356 IF l_debug_level > 0 THEN
357 oe_debug_pub.add( 'EXITING GET_OTA DESCRIPTION'|| L_DESCRIPTION , 1 ) ;
358 END IF;
359 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
360 IF l_debug_level > 0 THEN
361 oe_debug_pub.add( 'GET_OTA_DESCRIPTION API - UNEXPECTED ERROR' ) ;
362 END IF;
363 IF l_debug_level > 0 THEN
364 oe_debug_pub.add( 'EXITING GET_OTA_DESCRIPTION API' ) ;
365 END IF;
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add( 'GET_OTA_DESCRIPTION API - ERROR' ) ;
370 END IF;
371 IF l_debug_level > 0 THEN
372 oe_debug_pub.add( 'EXITING GET_OTA_DESCRIPTION API' ) ;
373 END IF;
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376
377 END IF;
378
379
380 End Get_OTA_Description;
381
382
383
384 PROCEDURE Check_OTA_Line( p_application_id IN NUMBER,
385 p_entity_short_name in VARCHAR2,
386 p_validation_entity_short_name in VARCHAR2,
387 p_validation_tmplt_short_name in VARCHAR2,
388 p_record_set_tmplt_short_name in VARCHAR2,
389 p_scope in VARCHAR2,
390 p_result OUT NOCOPY NUMBER ) is
391
392
393
394 l_line_id NUMBER := oe_line_security.g_record.line_id;
395 l_quantity_uom VARCHAR2(3);
396
397 --
398 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
399 --
400 BEGIN
401
402 select order_quantity_uom into l_quantity_uom
403 from oe_order_lines_all where
404 line_id = l_line_id;
405 if l_quantity_uom IN ('ENR','EVT') then
406 p_result := 1;
407 else
408 p_result := 0;
409 end if;
410
411
412 EXCEPTION
413 WHEN no_data_found then
414 p_result := 0;
415
416 END Check_OTA_Line;
417
418 /* Function: Is_OTA_Line */
419
420 FUNCTION Is_OTA_Line
421 (p_order_quantity_uom VARCHAR2 := FND_API.G_MISS_CHAR)
422 RETURN BOOLEAN
423 IS
424
425 --
426 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
427 --
428 BEGIN
429
430 IF l_debug_level > 0 THEN
431 oe_debug_pub.add( 'ENTERING IS_OTA_LINE FUNCTION ' , 1 ) ;
432 END IF;
433 IF p_order_quantity_uom <> FND_API.G_MISS_CHAR THEN
434 -- check the uom value
435 IF p_order_quantity_uom IN ('ENR','EVT') THEN
436 RETURN TRUE;
437 ELSE
438 RETURN FALSE;
439 END IF;
440 ELSE
441 RAISE FND_API.G_EXC_ERROR;
442 END IF;
443 IF l_debug_level > 0 THEN
444 oe_debug_pub.add( 'EXITING IS_OTA_LINE FUNCTION ' , 1 ) ;
445 END IF;
446
447 EXCEPTION
448 when others then
449 IF l_debug_level > 0 THEN
450 oe_debug_pub.add( 'EXCEPTION IN IS_OTA_LINE FUNCTION ' , 1 ) ;
451 END IF;
452 RETURN FALSE;
453 END Is_OTA_Line;
454
455
456 /* csheu create new procedure Create_OTA_Enroll */
457
458 PROCEDURE Create_OTA_Enroll(p_line_id IN NUMBER,
459 p_org_id IN NUMBER,
460 p_sold_to_org_id IN NUMBER,
461 p_ship_to_org_id IN NUMBER,
462 p_sold_to_contact_id IN NUMBER,
463 p_ship_to_contact_id IN NUMBER,
464 p_event_id IN NUMBER,
465 p_order_date IN DATE,
466 x_enrollment_id OUT NOCOPY NUMBER,
467
468 x_enrollment_status OUT NOCOPY VARCHAR2,
469
470 x_return_status OUT NOCOPY VARCHAR2)
471
472
473 IS
474
475 l_sql_stat VARCHAR2(3000);
476 l_return_status VARCHAR2(1);
477 l_enrollment_id NUMBER;
478 l_enrollment_status VARCHAR2(30);
479
480
481 --
482 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
483 --
484 BEGIN
485
486
487 IF l_debug_level > 0 THEN
488 oe_debug_pub.add( 'ENTERING CREATE_OTA_ENROLL' , 1 ) ;
489 END IF;
490 -- IF Get_Product_Status(810) IN ('I','S') THEN
491
492 -- lkxu, for bug 1701377
493 IF OE_GLOBALS.G_OTA_INSTALLED IS NULL THEN
494 OE_GLOBALS.G_OTA_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(810);
495 END IF;
496
497 IF OE_GLOBALS.G_OTA_INSTALLED = 'Y' THEN
498
499 -- Call the OTA API to create enrollment
500
501 l_sql_stat := '
502 Begin
503 OTA_OM_UPD_API.CREATE_ENROLL_FROM_OM(
504 :p_line_id
505 ,:p_org_id
506 ,:p_sold_to_org_id
507 ,:p_ship_to_org_id
508 ,:p_sold_to_contact_id
509 ,:p_ship_to_contact_id
510 ,:p_event_id
511 ,:p_order_date
512 ,:x_enrollment_id
513 ,:x_enrollment_status
514 ,:x_return_status
515 );
516 END;';
517 EXECUTE IMMEDIATE l_sql_stat
518 USING IN p_line_id
519 , IN p_org_id
520 , IN p_sold_to_org_id
521 , IN p_ship_to_org_id
522 , IN p_sold_to_contact_id
523 , IN p_ship_to_contact_id
524 , IN p_event_id
525 , IN p_order_date
526 , OUT l_enrollment_id
527
528 , OUT l_enrollment_status
529
530 , OUT l_return_status;
531
532
533 x_return_status := l_return_status;
534 x_enrollment_id := l_enrollment_id;
535 x_enrollment_status := l_enrollment_status;
536
537
538 IF l_debug_level > 0 THEN
539 oe_debug_pub.add( 'EXITING CREATE OTA ENROLL '|| L_RETURN_STATUS , 1 ) ;
540 END IF;
541 IF l_debug_level > 0 THEN
542 oe_debug_pub.add( 'EXITING CREATE OTA ENROLL '|| L_ENROLLMENT_STATUS , 1 ) ;
543 END IF;
544 IF l_debug_level > 0 THEN
545 oe_debug_pub.add( 'EXITING CREATE OTA ENROLL '|| L_RETURN_STATUS , 1 ) ;
546 END IF;
547
548 END IF;
549 END Create_OTA_Enroll;
550
551
552 -----------------------------------------
553 -- Function name: Get_OTA_Event_End_Date
554 -- Abstract: Given a line_id and UOM, return the event
555 -- information associated with the order line.
556 -- This API is called during cross item validation
557 -- for commitment.
558 ------------------------------------------
559 Function Get_OTA_Event_End_Date
560 (p_line_id IN NUMBER,
561 p_UOM IN VARCHAR2)
562 RETURN DATE
563 IS
564
565 l_sql_stat VARCHAR2(3000);
566 l_activity_name VARCHAR2(2000);
567 l_event_title VARCHAR2(2000);
568 l_course_start_date DATE;
569 l_course_end_date DATE;
570
571 --
572 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
573 --
574 BEGIN
575
576 IF l_debug_level > 0 THEN
577 oe_debug_pub.add( 'ENTERING GET_OTA_EVENT_END_DATE' , 1 ) ;
578 END IF;
579
580 IF OE_GLOBALS.G_OTA_INSTALLED IS NULL THEN
581 OE_GLOBALS.G_OTA_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(810);
582 END IF;
583
584 IF OE_GLOBALS.G_OTA_INSTALLED = 'Y' THEN
585
586 l_sql_stat := '
587 Begin
588 OTA_OM_UTIL.Get_Event_Detail(
589 :p_line_id
590 ,:p_uom
591 ,:x_activity_name
592 ,:x_event_title
593 ,:x_course_start_date
594 ,:x_course_end_date);
595 END;';
596
597 EXECUTE IMMEDIATE l_sql_stat
598 USING IN p_line_id
599 , IN p_uom
600 , OUT l_activity_name
601
602 , OUT l_event_title
603
604 , OUT l_course_start_date
605
606 , OUT l_course_end_date;
607
608
609 IF l_debug_level > 0 THEN
610 oe_debug_pub.add( 'EXITING GET_OTA_EVENT_END_DATE WITH EVENT END DATE' , 1 ) ;
611 END IF;
612 RETURN l_course_end_date;
613
614 END IF;
615
616 IF l_debug_level > 0 THEN
617 oe_debug_pub.add( 'EXITING GET_OTA_EVENT_END_DATE WITH NULL' , 1 ) ;
618 END IF;
619 RETURN NULL;
620
621
622 EXCEPTION
623
624 WHEN OTHERS THEN
625
626 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
627 THEN
628 OE_MSG_PUB.Add_Exc_Msg
629 ( G_PKG_NAME
630 , 'Get_OTA_Event_End_Date'
631 );
632 END IF;
633
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635
636
637 End Get_OTA_Event_End_Date;
638
639 END OE_OTA_UTIL;
640
[Home] [Help]