1 package body jg_info as
2 /* $Header: jgzzinfb.pls 120.2 2005/02/17 15:25:33 ahansen ship $ */
3 procedure jg_get_period_dates (app_id IN NUMBER,
4 tset_of_books_id IN NUMBER,
5 tperiod_name IN VARCHAR2,
6 tstart_date OUT NOCOPY DATE,
7 tend_date OUT NOCOPY DATE,
8 errbuf OUT NOCOPY VARCHAR2)
9 IS
10 BEGIN
11 select start_date, end_date
12 into tstart_date, tend_date
13 from gl_period_statuses
14 where period_name = tperiod_name
15 and ledger_id = tset_of_books_id -- 11ix
16 and application_id = app_id ;
17 EXCEPTION
18 WHEN NO_DATA_FOUND THEN
19 fnd_message.set_name('SQLGL','GL_PLL_INVALID_PERIOD') ;
20 fnd_message.set_token('PERIOD',tperiod_name) ;
21 fnd_message.set_token('SOBID',to_char(tset_of_books_id)) ;
22 errbuf := fnd_message.get ;
23
24 WHEN OTHERS THEN
25 errbuf := SQLERRM;
26 END;
27 procedure jg_get_set_of_books_info ( sobid IN NUMBER,
28 coaid OUT NOCOPY NUMBER,
29 sobname OUT NOCOPY VARCHAR2,
30 func_curr OUT NOCOPY VARCHAR2,
31 errbuf OUT NOCOPY VARCHAR2)
32 IS
33 BEGIN
34 select name, chart_of_accounts_id, currency_code
35 into sobname, coaid, func_curr
36 from gl_sets_of_books
37 where set_of_books_id = sobid;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 fnd_message.set_name('SQLGL','GL_PLL_INVALID_SOB') ;
41 fnd_message.set_token('SOBID',to_char(sobid)) ;
42 errbuf := fnd_message.get ;
43 WHEN OTHERS THEN
44 errbuf := SQLERRM;
45 END;
46 procedure jg_get_bud_or_enc_name ( actual_type IN VARCHAR2,
47 type_id IN NUMBER,
48 name OUT NOCOPY VARCHAR2,
49 errbuf OUT NOCOPY VARCHAR2)
50 IS
51 BEGIN
52 if (actual_type = 'B') then
53 select bv.budget_name
54 into name
55 from gl_budget_versions bv
56 where bv.budget_version_id = type_id;
57 elsif (actual_type = 'E') then
58 select e.encumbrance_type
59 into name
60 from gl_encumbrance_types e
61 where e.encumbrance_type_id = type_id;
62 end if;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 if (actual_type = 'B') then
66 /* There does not exist a budget with budget version id BUDID */
67 fnd_message.set_name('SQLGL','GL_PLL_INVALID_BUDGET_VERSION') ;
68 fnd_message.set_token('BUDID',to_char(type_id)) ;
69 errbuf := fnd_message.get ;
70 else
71 /* There does not exist an encumbrance type with id ENCID */
72 fnd_message.set_name('SQLGL','GL_PLL_INVALID_ENC_TYPE') ;
73 fnd_message.set_token('ENCID',to_char(type_id)) ;
74 errbuf := fnd_message.get ;
75 end if;
76 WHEN OTHERS THEN
77 errbuf := SQLERRM;
78 END;
79 procedure jg_get_lookup_value ( lmode VARCHAR2,
80 code VARCHAR2,
81 type VARCHAR2,
82 value OUT NOCOPY VARCHAR2,
83 errbuf OUT NOCOPY VARCHAR2)
84 IS
85 BEGIN
86 select decode(lmode, 'M', meaning, description)
87 into value
88 from gl_lookups
89 where lookup_code = code
90 and lookup_type = type;
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 /* The lookup code CODE of lookup type TYPE is missing */
94 fnd_message.set_name('SQLGL','GL_PLL_MISSING_LOOKUP') ;
95 fnd_message.set_token('CODE',code);
96 fnd_message.set_token('TYPE',type);
97 errbuf := fnd_message.get ;
98 WHEN OTHERS THEN
99 errbuf := SQLERRM;
100 END;
101 procedure jg_get_first_period(app_id IN NUMBER,
102 tset_of_books_id IN NUMBER,
103 tperiod_name IN VARCHAR2,
104 tfirst_period OUT NOCOPY VARCHAR2,
105 errbuf OUT NOCOPY VARCHAR2)
106 IS
107 BEGIN
108 SELECT a.period_name
109 INTO tfirst_period
110 FROM gl_period_statuses a, gl_period_statuses b
111 WHERE a.application_id = app_id
112 AND b.application_id = app_id
113 AND a.ledger_id = tset_of_books_id -- 11ix
114 AND b.ledger_id = tset_of_books_id -- 11ix
115 AND a.period_type = b.period_type
116 AND a.period_year = b.period_year
117 AND b.period_name = tperiod_name
118 AND a.period_num =
119 (SELECT min(c.period_num)
120 FROM gl_period_statuses c
121 WHERE c.application_id = app_id
122 AND c.ledger_id = tset_of_books_id -- 11ix
123 AND c.period_year = a.period_year
124 AND c.period_type = a.period_type
125 GROUP BY c.period_year);
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 fnd_message.set_name('SQLGL','GL_PLL_INVALID_FIRST_PERIOD') ;
129 fnd_message.set_token('PERIOD',tperiod_name) ;
130 fnd_message.set_token('SOBID',to_char(tset_of_books_id)) ;
131 errbuf := fnd_message.get ;
132 WHEN OTHERS THEN
133 errbuf := SQLERRM;
134 END;
135 procedure jg_get_first_period_of_quarter(app_id IN NUMBER,
136 tset_of_books_id IN NUMBER,
137 tperiod_name IN VARCHAR2,
138 tfirst_period OUT NOCOPY VARCHAR2,
139 errbuf OUT NOCOPY VARCHAR2)
140 IS
141 BEGIN
142 SELECT a.period_name
143 INTO tfirst_period
144 FROM gl_period_statuses a, gl_period_statuses b
145 WHERE a.application_id = app_id
146 AND b.application_id = app_id
147 AND a.ledger_id = tset_of_books_id -- 11ix
148 AND b.ledger_id = tset_of_books_id -- 11ix
149 AND a.period_type = b.period_type
150 AND a.period_year = b.period_year
151 AND a.quarter_num = b.quarter_num
152 AND b.period_name = tperiod_name
153 AND a.period_num =
154 (SELECT min(c.period_num)
155 FROM gl_period_statuses c
156 WHERE c.application_id = app_id
157 AND c.ledger_id = tset_of_books_id -- 11ix
158 AND c.period_year = a.period_year
159 AND c.quarter_num = a.quarter_num
160 AND c.period_type = a.period_type
161 GROUP BY c.period_year,c.quarter_num);
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 fnd_message.set_name('SQLGL','GL_PLL_INVALID_FIRST_PERIOD') ;
165 fnd_message.set_token('PERIOD',tperiod_name) ;
166 fnd_message.set_token('SOBID',to_char(tset_of_books_id)) ;
167 errbuf := fnd_message.get ;
168 WHEN OTHERS THEN
169 errbuf := SQLERRM;
170 END;
171 procedure jg_get_consolidation_info(
172 cons_id NUMBER, cons_name OUT NOCOPY VARCHAR2,
173 method OUT NOCOPY VARCHAR2, curr_code OUT NOCOPY VARCHAR2,
174 from_sobid OUT NOCOPY NUMBER, to_sobid OUT NOCOPY NUMBER,
175 description OUT NOCOPY VARCHAR2, start_date OUT NOCOPY DATE,
176 end_date OUT NOCOPY DATE, errbuf OUT NOCOPY VARCHAR2) is
177 begin
178 select glc.name, glc.method, glc.from_currency_code,
179 glc.from_ledger_id, glc.to_ledger_id, -- 11ix
180 glc.description, glc.start_date_active_11i, glc.end_date_active_11i
181 into cons_name, method, curr_code, from_sobid, to_sobid,
182 description, start_date, end_date
183 from gl_consolidation glc
184 where glc.consolidation_id = cons_id;
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 fnd_message.set_name('SQLGL','GL_PLL_INVALID_CONSOLID_ID') ;
188 fnd_message.set_token('CID', to_char(cons_id));
189 errbuf := fnd_message.get ;
190 end;
191
192 FUNCTION jg_format_curr_amt(in_precision NUMBER,
193 in_amount_disp VARCHAR2) return VARCHAR2
194 IS
195 char_str VARCHAR2(1);
196 str_length NUMBER;
197 new_string VARCHAR2(40);
198 count_pos NUMBER;
199 done BOOLEAN;
200
201 BEGIN
202
203 done := FALSE;
204 IF ((in_precision > 0) AND (in_amount_disp IS NOT NULL)) THEN
205
206 str_length := nvl(length(ltrim(rtrim(in_amount_disp))),0);
207 count_pos := 0;
208
209 WHILE ((NOT done) or (count_pos + str_length > 0)) LOOP
210 count_pos := count_pos - 1;
211 char_str := substrb(in_amount_disp,count_pos , 1);
212
213 IF ((char_str = ',') and (NOT done)) THEN
214 new_string := REPLACE(in_amount_disp,',','$');
215 done := TRUE;
216 END IF;
217
218 IF ((char_str = '.') AND (NOT done)) THEN
219 new_string := TRANSLATE(in_amount_disp,'.,','$.');
220 done := TRUE;
221 END IF;
222
223 END LOOP;
224
225 ELSE
226 new_string := replace(in_amount_disp,',','.');
227
228 END IF;
229
230 RETURN(new_string);
231
232 EXCEPTION WHEN OTHERS THEN
233 RETURN(in_amount_disp);
234
235 END jg_format_curr_amt;
236 end jg_info;
[Home] [Help]