1 PACKAGE BODY cn_general_utils AS
2 -- $Header: cnsygutb.pls 120.1 2005/08/10 03:46:43 hithanki noship $
3 /*
4 Package Body Name
5 cn_general_utils
6 Purpose
7 This package consists of general utilities used throughout commissions.
8 History
9 06-SEP-94 P Cook Created by removing the non-generation specific
10 procedures and functions from cn_utils.
11 17-JUL-95 P Cook No longer raise app_exception when no rows found
12 in from period packages
13 06-NOV-95 P Cook Bug:320828. get_currency. Return an error message
14 when no sob in cn_repositories.
15 04-APR-96 A Saxena Added function get_set_of_books_id
16 03-JUN-96 A Saxena Added function get_currency_CODE
17 */
18
19 --
20 -- Function Name
21 -- get_set_of_books_id
22 -- Purpose
23 -- Get set of books id for current instance of CN
24 -- Assumes always will be exactly 1 record in CN_REPOSITORIES
25
26 FUNCTION get_set_of_books_id RETURN NUMBER is
27 SOB_ID number;
28 begin
29 select SET_OF_BOOKS_ID
30 into SOB_ID
31 from CN_REPOSITORIES;
32 return(SOB_ID);
33 end;
34
35
36 --
37 -- Function Name
38 -- get_currency
39 -- Purpose
40 -- Get currency code for the current set of books
41 --
42
43 FUNCTION get_currency(p_org_id NUMBER) RETURN VARCHAR2 IS x_currency_code VARCHAR2(15);
44
45 BEGIN
46 SELECT s.currency_code
47 INTO x_currency_code
48 FROM gl_sets_of_books s
49 ,cn_repositories_all r
50 WHERE r.set_of_books_id = s.set_of_books_id
51 AND r.application_id = 283
52 AND r.org_id = p_org_id
53 ;
54
55 RETURN x_currency_code;
56
57 EXCEPTION
58
59 WHEN no_data_found THEN
60 fnd_message.set_name('CN','ALL_NO_INSTANCE_INFO');
61 app_exception.raise_exception;
62
63 END get_currency;
64
65 --
66 -- Function Name
67 -- get_currency_code
68 -- Purpose
69 -- Get currency code for the current set of books
70 -- Differs from above in that it is guaranteed not to update database
71 -- I.e., can be selected from sys.dual
72 --
73
74 FUNCTION get_currency_code RETURN VARCHAR2 IS x_currency_code VARCHAR2(15);
75
76 BEGIN
77 SELECT s.currency_code
78 INTO x_currency_code
79 FROM gl_sets_of_books s
80 ,cn_repositories r
81 WHERE r.set_of_books_id = s.set_of_books_id
82 AND r.application_id = 283
83 ;
84
85 RETURN x_currency_code;
86
87 END get_currency_code;
88
89 --
90 -- Procedure Name
91 -- get_period_id
92 -- Purpose
93 -- Get period for given date
94 --
95
96 FUNCTION get_period_id (X_period_date DATE)
97 RETURN NUMBER IS X_period_id NUMBER;
98 BEGIN
99 SELECT period_id
100 INTO X_period_id
101 FROM cn_periods
102 WHERE X_period_date BETWEEN trunc(start_date)
103 AND trunc(end_date)
104 ;
105 RETURN X_period_id;
106
107 EXCEPTION
108 WHEN no_data_found THEN
109 RETURN null;
110 -- fnd_message.set_name('CN','ALL_NO_PERIOD_FOUND');
111 -- app_exception.raise_exception;
112
113 END get_period_id;
114
115 --
116 -- Procedure Name
117 -- get_period_info
118 -- Purpose
119 --
120 --
121
122 PROCEDURE get_period_info ( x_period_date IN DATE
123 ,x_period_id IN OUT NOCOPY NUMBER
124 ,x_period_name IN OUT NOCOPY VARCHAR2
125 ,x_period_status OUT NOCOPY VARCHAR2
126 ,x_start_date OUT NOCOPY DATE
127 ,x_end_date OUT NOCOPY DATE) IS
128
129 BEGIN
130
131 IF ( x_period_date IS NOT NULL
132 OR x_period_name IS NOT NULL
133 OR x_period_id IS NOT NULL) THEN
134
135 SELECT p.period_id
136 ,p.period_status
137 ,p.period_name
138 ,p.start_date
139 ,p.end_date
140 INTO x_period_id
141 ,x_period_status
142 ,x_period_name
143 ,x_start_date
144 ,x_end_date
145 FROM cn_periods p
146 WHERE ( x_period_date IS NULL
147 OR x_period_date BETWEEN trunc(p.start_date)
148 AND trunc(p.end_date) )
149 AND ( x_period_name IS NULL
150 OR x_period_name = p.period_name)
151 AND ( x_period_id IS NULL
152 OR x_period_id = p.period_id)
153 ;
154
155 END IF;
156
157 EXCEPTION
158 WHEN no_data_found THEN
159 null; -- The calling routine must decide what to do in this case
160 --fnd_message.set_name('CN','ALL_NO_PERIOD_FOUND');
161 --app_exception.raise_exception;
162
163
164 END get_period_info;
165
166 -- Procedure Name
167 -- get_period_name
168 -- Purpose
169 -- Get period name for given period_id
170 --
171
172 FUNCTION get_period_name (X_period_id NUMBER)
173 RETURN VARCHAR2 IS X_period_name VARCHAR2(30);
174 BEGIN
175 SELECT period_name
176 INTO X_period_name
177 FROM cn_periods
178 WHERE period_id = X_period_id
179 ;
180 RETURN X_period_name;
181
182 EXCEPTION
183 WHEN no_data_found THEN
184 RETURN null;
185 -- fnd_message.set_name('CN','ALL_NO_PERIOD_FOUND');
186 -- app_exception.raise_exception;
187
188 END get_period_name;
189
190
191 --
192 -- Procedure Name
193 -- get_meaning
194 -- Purpose
195 -- Get meaning for given lookup type and code
196
197 FUNCTION get_meaning (X_lookup_code VARCHAR2,
198 X_lookup_type VARCHAR2)
199 RETURN varchar2 IS X_meaning VARCHAR2(80);
200
201 BEGIN
202 SELECT meaning
203 INTO X_meaning
204 FROM cn_lookups
205 WHERE lookup_code = X_lookup_code
206 AND lookup_type = X_lookup_type;
207
208 RETURN X_meaning;
209
210 EXCEPTION
211 WHEN no_data_found THEN
212 RAISE no_data_found;
213
214 END get_meaning;
215
216
217 --
218 -- Procedure Name
219 -- Get_Salesrep_info
220 -- Purpose
221 --
222
223 PROCEDURE get_salesrep_info ( X_Salesrep_id NUMBER
224 ,X_Name IN OUT NOCOPY VARCHAR2
225 ,X_employee_number IN OUT NOCOPY NUMBER) IS
226 BEGIN
227
228 SELECT name
229 ,employee_number
230 INTO x_name
231 ,x_employee_number
232 FROM cn_salesreps
233 WHERE salesrep_id = X_Salesrep_id
234 ;
235
236 EXCEPTION
237 WHEN no_data_found THEN
238 RAISE no_data_found;
239
240 END get_salesrep_info;
241
242 --
243 -- Procedure Name
244 --
245 -- Purpose
246 --
247
248 FUNCTION get_username (X_userid number) RETURN varchar2 IS
249 X_username VARCHAR2(100);
250 BEGIN
251
252 SELECT user_name
253 INTO X_username
254 FROM fnd_user
255 WHERE user_id = X_userid;
256
257 return X_username;
258
259 EXCEPTION
260 when no_data_found then
261 raise no_data_found;
262
263 END get_username;
264
265 END cn_general_utils;
[Home] [Help]