1 PACKAGE BODY JTF_UMUTIL as
2 /* $Header: JTFUMLDB.pls 115.3 2002/02/14 12:09:36 pkm ship $ */
3
4 --
5 -- PUBLIC
6 --
7
8 -----------------------------------------------------------------------------------
9 /* Function to lookup usertype_id based on usertype_key and effective start date */
10 -----------------------------------------------------------------------------------
11 function usertype_lookup(
12 utype_key IN varchar2,
13 effective_date IN date)
14 return number is
15 utype_id number;
16 begin
17
18 select usertype_id
19 into utype_id
20 from jtf_um_usertypes_b
21 where usertype_key = utype_key
22 and effective_start_date = effective_date;
23
24 return utype_id;
25
26 exception
27 when NO_DATA_FOUND then
28 return NULL;
29
30 end usertype_lookup;
31
32 -------------------------------------------------------------------------------------------
33 /* Function to lookup subscription_id based on subscription_key and effective start date */
34 -------------------------------------------------------------------------------------------
35 function subscription_lookup(
36 subscr_key IN varchar2,
37 effective_date IN date)
38 return number is
39 subscr_id number;
40 begin
41
42 select subscription_id
43 into subscr_id
44 from jtf_um_subscriptions_b
45 where subscription_key = subscr_key
46 and effective_start_date = effective_date;
47
48 return subscr_id;
49
50 exception
51 when NO_DATA_FOUND then
52 return NULL;
53
54 end subscription_lookup;
55
56 -----------------------------------------------------------------------------------
57 /* Function to lookup template_id based on template_key and effective start date */
58 -----------------------------------------------------------------------------------
59 function template_lookup(
60 tmpl_key IN varchar2,
61 effective_date IN date)
62 return number is
63 tmpl_id number;
64 begin
65
66 select template_id
67 into tmpl_id
68 from jtf_um_templates_b
69 where template_key = tmpl_key
70 and effective_start_date = effective_date;
71
72 return tmpl_id;
73
74 exception
75 when NO_DATA_FOUND then
76 return NULL;
77
78 end template_lookup;
79
80 -----------------------------------------------------------------------------------
81 /* Function to lookup approval_id based on approval_key and effective start date */
82 -----------------------------------------------------------------------------------
83 function approval_lookup(
84 appr_key IN varchar2,
85 effective_date IN date)
86 return number is
87 appr_id number;
88 begin
89
90 select approval_id
91 into appr_id
92 from jtf_um_approvals_b
93 where approval_key = appr_key
94 and effective_start_date = effective_date;
95
96 return appr_id;
97
98 exception
99 when NO_DATA_FOUND then
100 return NULL;
101
102 end approval_lookup;
103
104 -----------------------------------------------------------------------------------
105 /* Function to lookup approval_id based on approval_key and effective start date */
106 /* Here, if ID not found, throw exception */
107 -----------------------------------------------------------------------------------
108 function approval_lookup_with_check(
109 appr_key IN varchar2,
110 effective_date IN date)
111 return number is
112 appr_id number;
113 begin
114
115 if( (appr_key is NULL) and (effective_date is null) )
116 then
117 return NULL;
118 end if;
119
120 select approval_id
121 into appr_id
122 from jtf_um_approvals_b
123 where approval_key = appr_key
124 and effective_start_date = effective_date;
125
126 return appr_id;
127
128 exception
129 when NO_DATA_FOUND then
130 -- raising exception here as approval_id does not exist
131 fnd_message.set_name('JTF', 'APPROVAL_ID_NOT_FOUND');
132 app_exception.raise_exception;
133
134 end approval_lookup_with_check;
135
136 -------------------------------------------------------------------------------------------
137 /* Function to lookup subscription_id based on subscription_key and effective start date */
138 /* Here, if ID not found, throw exception */
139 -------------------------------------------------------------------------------------------
140 function subscription_lookup_with_check(
141 subscr_key IN varchar2,
142 effective_date IN date)
143 return number is
144 subscr_id number;
145 begin
146
147 if( (subscr_key is NULL) and (effective_date is null) )
148 then
149 return NULL;
150 end if;
151
152 select subscription_id
153 into subscr_id
154 from jtf_um_subscriptions_b
155 where subscription_key = subscr_key
156 and effective_start_date = effective_date;
157
158 return subscr_id;
159
160 exception
161 when NO_DATA_FOUND then
162 -- raising exception here as approval_id does not exist
163 fnd_message.set_name('JTF', 'SUBSCRIPTION_ID_NOT_FOUND');
164 app_exception.raise_exception;
165
166 end subscription_lookup_with_check;
167
168 -----------------------------------------------------------------------------------
169 /* Function to lookup username based on user_id */
170 -----------------------------------------------------------------------------------
171 function user_lookup(
172 uname IN varchar2)
173 return number is
174 u_id number;
175 begin
176
177 select user_id
178 into u_id
179 from fnd_user
180 where user_name = uname;
181
182 return u_id;
183
184 exception
185 when NO_DATA_FOUND then
186 return NULL;
187
188 end user_lookup;
189
190
191 function date_to_char(
192 inDate IN date)
193 return varchar2 is
194 returnVal VARCHAR2(19);
195 begin
196
197 return to_char( inDate, 'YYYY/MM/DD HH24:MI:SS' );
198
199 end date_to_char;
200 end JTF_UMUTIL;
[Home] [Help]