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