1 PACKAGE BODY xnp_def_jeopardy_int AS
2 /* $Header: XNPJINTB.pls 120.1 2005/06/21 04:07:32 appldev ship $ */
3
4 --------------------------------------------------------------------------------
5 ----- API Name : get_interval
6 ----- Type : Private
7 ----- Purpose : Get interval for the default jeopardy timer.
8 ----- Parameters : p_order_id
9 --------------------------------------------------------------------------------
10
11
12 /*
13 -- This function was truncating the Time component in the due date
14 -- also it was nota taking care of the Due date already passed
15 -- scenario
16 -- All these errors have been corrected in the new function
17
18 FUNCTION get_interval ( p_order_id IN NUMBER)
19 RETURN number
20 IS
21
22 l_due_date DATE;
23 l_interval NUMBER;
24 l_due_date_string VARCHAR2(40);
25 e_DUE_DATE_NOT_FOUND EXCEPTION;
26
27 begin
28 l_interval := 0;
29
30 -- Select Due Date from xdp_oe_order_headers
31 select NVL(to_char(due_date),'NOT FOUND')
32 into l_due_date_string
33 --FROM xdp_oe_order_headers
34 --skilaru 03/27/2001
35 from xdp_order_headers
36 where order_id = p_order_id;
37
38 IF l_due_date_string = 'NOT FOUND'
39 THEN
40 RAISE e_DUE_DATE_NOT_FOUND;
41 ELSE
42 l_due_date := to_date(l_due_date_string);
43
44 l_interval := (l_due_date - sysdate)*24*60*60;
45
46 return l_interval;
47
48 END IF;
49
50 EXCEPTION
51 WHEN e_DUE_DATE_NOT_FOUND THEN
52 fnd_message.set_name('XNP','XNP_JEP_DUE_DATE_NOT_FOUND');
53 fnd_message.set_token('ORDER_ID',p_order_id);
54 APP_EXCEPTION.RAISE_EXCEPTION;
55 return NULL;
56 end;
57 */
58
59 -- Get Jeopardy interval in seconds for the Order using Due date
60 -- If Due date is already passed then return 0
61
62
63 FUNCTION get_interval (p_order_id IN NUMBER)
64 RETURN number
65 IS
66 l_due_date DATE;
67 l_interval NUMBER;
68 e_DUE_DATE_NOT_FOUND EXCEPTION;
69
70 BEGIN
71
72 SELECT due_date
73 INTO l_due_date
74 --FROM xdp_oe_order_headers
75 --skilaru 03/27/2001
76 FROM xdp_order_headers
77 WHERE order_id = p_order_id;
78
79
80 IF l_due_date IS NULL THEN
81 RAISE e_DUE_DATE_NOT_FOUND;
82 ELSE
83 l_interval := (l_due_date - sysdate)*24*60*60;
84
85 -- If Due date is already passed then in Jeopardy
86 IF l_interval < 0 THEN
87 l_interval := 0;
88 END IF;
89
90 return l_interval;
91
92 END IF;
93
94 EXCEPTION
95 WHEN e_DUE_DATE_NOT_FOUND THEN
96 fnd_message.set_name('XNP','XNP_JEP_DUE_DATE_NOT_FOUND');
97 fnd_message.set_token('ORDER_ID',p_order_id);
98 APP_EXCEPTION.RAISE_EXCEPTION;
99 RETURN null;
100 END get_interval;
101
102
103 END xnp_def_jeopardy_int;