1 PACKAGE BODY GL_FC_XLATE_PKG AS
2 /* $Header: glfcxltb.pls 120.8 2005/05/05 02:05:19 kvora ship $ */
3
4 FUNCTION get_unique_name(ldg_name IN VARCHAR2,
5 ldg_id IN NUMBER,
6 tcurr_code IN VARCHAR2) RETURN VARCHAR2 IS
7 dummy number;
8 target_lname VARCHAR2(30);
9 CURSOR unique_name(target_lname VARCHAR2) IS
10 SELECT 1
11 FROM DUAL
12 WHERE EXISTS
13 (SELECT primary_ledger_id
14 FROM gl_ledger_relationships
15 WHERE target_ledger_name = target_lname);
16 BEGIN
17 target_lname := substrb(ldg_name,1,(27-lengthb(tcurr_code)))
18 ||' ('||tcurr_code||')';
19
20 OPEN unique_name(target_lname);
21 FETCH unique_name INTO dummy;
22
23 IF(dummy = 1) THEN
24 SELECT decode(sign(26-lengthb(to_char(ldg_id))-lengthb(tcurr_code)),
25 1,substrb(ldg_name,1,
26 (26-lengthb(ldg_id)-lengthb(tcurr_code)))||
27 ' ','')||to_char(ldg_id)||' ('||
28 decode(sign(27-lengthb(to_char(ldg_id))-lengthb(tcurr_code)),
29 -1,substrb(tcurr_code,1,
30 (27-lengthb(to_char(ldg_id))))||
31 ')',tcurr_code||')')
32 INTO target_lname
33 FROM DUAL;
34 END IF;
35
36 RETURN target_lname;
37
38 END get_unique_name;
39
40
41 FUNCTION get_unique_short_name(ldg_short_name IN VARCHAR2,
42 ldg_id IN NUMBER,
43 tcurr_code IN VARCHAR2) RETURN VARCHAR2 IS
44 dummy number;
45 target_lshort_name VARCHAR2(30);
46 CURSOR unique_name(target_lshort_name VARCHAR2) IS
47 SELECT 1
48 FROM DUAL
49 WHERE EXISTS
50 (SELECT primary_ledger_id
51 FROM gl_ledger_relationships
52 WHERE target_ledger_short_name = target_lshort_name);
53 BEGIN
54 target_lshort_name := substrb(ldg_short_name,1,(17-lengthb(tcurr_code)))
55 ||' ('||tcurr_code||')';
56
57 OPEN unique_name(target_lshort_name);
58 FETCH unique_name INTO dummy;
59
60 IF(dummy = 1) THEN
61 SELECT decode(sign(16-lengthb(to_char(ldg_id))-lengthb(tcurr_code)),
62 1,substrb(ldg_short_name,1,
63 (16-lengthb(ldg_id)-lengthb(tcurr_code)))||' ','')||
64 to_char(ldg_id)||' ('||
65 decode(sign(17-lengthb(to_char(ldg_id))-lengthb(tcurr_code)),
66 -1,substrb(tcurr_code,1,(17-lengthb(to_char(ldg_id))))||
67 ')',tcurr_code||')')
68 INTO target_lshort_name
69 FROM DUAL;
70 END IF;
71
72 RETURN target_lshort_name;
73
74 END get_unique_short_name;
75
76
77 FUNCTION get_ledger_name(ldg_name IN VARCHAR2,
78 ldg_id IN NUMBER,
79 tcurr_code IN VARCHAR2) RETURN VARCHAR2 IS
80 tgt_ldg_name VARCHAR2(30);
81
82 CURSOR get_existing_ledger_name IS
83 SELECT target_ledger_name
84 FROM gl_ledger_relationships
85 WHERE target_ledger_id = ldg_id
86 AND target_currency_code = tcurr_code
87 AND application_id = 101
88 AND org_id = -99
89 AND relationship_enabled_flag = 'Y';
90
91 BEGIN
92 -- Stat is a special case where you just get the original ledger name
93 IF tcurr_code = 'STAT' THEN
94 return ldg_name;
95 END IF;
96
97 OPEN get_existing_ledger_name;
98 FETCH get_existing_ledger_name INTO tgt_ldg_name;
99 IF get_existing_ledger_name%NOTFOUND THEN
100 tgt_ldg_name := get_unique_name(ldg_name, ldg_id, tcurr_code);
101 END IF;
102 CLOSE get_existing_ledger_name;
103
104 return tgt_ldg_name;
105
106 END get_ledger_name;
107
108
109 FUNCTION get_ledger_short_name(ldg_short_name IN VARCHAR2,
110 ldg_id IN NUMBER,
111 tcurr_code IN VARCHAR2) RETURN VARCHAR2 IS
112 tgt_ldg_short_name VARCHAR2(30);
113
114 CURSOR get_existing_ledger_short_name IS
115 SELECT target_ledger_short_name
116 FROM gl_ledger_relationships
117 WHERE target_ledger_id = ldg_id
118 AND target_currency_code = tcurr_code
119 AND application_id = 101
120 AND org_id = -99
121 AND relationship_enabled_flag = 'Y';
122
123 BEGIN
124 -- Stat is a special case where you just get the original ledger short name
125 IF tcurr_code = 'STAT' THEN
126 return ldg_short_name;
127 END IF;
128
129 OPEN get_existing_ledger_short_name;
130 FETCH get_existing_ledger_short_name INTO tgt_ldg_short_name;
131 IF get_existing_ledger_short_name%NOTFOUND THEN
132 tgt_ldg_short_name :=
133 get_unique_short_name(ldg_short_name, ldg_id, tcurr_code);
134 END IF;
135 CLOSE get_existing_ledger_short_name;
136
137 return tgt_ldg_short_name;
138
139 END get_ledger_short_name;
140
141
142 FUNCTION relation_exist(ldg_id IN NUMBER,
143 tcurr_code IN VARCHAR2) RETURN VARCHAR2 IS
144 dummy NUMBER;
145 CURSOR relation IS
146 select l.ledger_id
147 from gl_ledgers l, gl_ledger_set_assignments lsa
148 where lsa.ledger_set_id = ldg_id
149 and l.ledger_id = lsa.ledger_id
150 and l.object_type_code = 'L'
151 and l.ledger_category_code in ('PRIMARY','SECONDARY')
152 and not exists
153 (select 1
154 from gl_ledger_relationships lr
155 where lr.source_ledger_id = l.ledger_id
156 and lr.target_ledger_id = l.ledger_id
157 and lr.target_ledger_category_code = 'ALC'
158 and lr.relationship_type_code = 'BALANCE'
159 and lr.target_currency_code = tcurr_code
160 and lr.application_id = 101
161 and lr.org_id = -99);
162
163 BEGIN
164 OPEN relation;
165 FETCH relation INTO dummy;
166
167 IF (relation%FOUND) THEN
168 RETURN 'N';
169 ELSE
170 RETURN 'Y';
171 END IF;
172
173 CLOSE relation;
174
175 END relation_exist;
176
177
178 FUNCTION xlated_ever(ldg_id IN NUMBER,
179 tcurr_code IN VARCHAR2,
180 bal_seg_val IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
181 dummy NUMBER;
182 CURSOR spec_bal IS
183 select l.ledger_id
184 from gl_ledgers l, gl_ledger_set_assignments lsa
185 where lsa.ledger_set_id = ldg_id
186 and l.ledger_id = lsa.ledger_id
187 and l.object_type_code = 'L'
188 and not exists
189 (select 1
190 from gl_translation_tracking tt
191 where tt.ledger_id = l.ledger_id
192 and tt.target_currency = tcurr_code
193 and tt.bal_seg_value = bal_seg_val);
194
195 CURSOR all_bal IS
196 select l.ledger_id
197 from gl_ledgers l, gl_ledger_set_assignments lsa
198 where lsa.ledger_set_id = ldg_id
199 and l.ledger_id = lsa.ledger_id
200 and l.object_type_code = 'L'
201 and not exists
202 (select 1
203 from gl_translation_tracking tt
204 where tt.ledger_id = l.ledger_id
205 and tt.target_currency = tcurr_code);
206
207 BEGIN
208 IF(bal_seg_val IS NULL) THEN
209 OPEN all_bal;
210 FETCH all_bal INTO dummy;
211 IF (all_bal%FOUND) THEN
212 RETURN 'N';
213 ELSE
214 RETURN 'Y';
215 END IF;
216 CLOSE all_bal;
217 ELSE
218 OPEN spec_bal;
219 FETCH spec_bal INTO dummy;
220 IF(spec_bal%FOUND) THEN
221 RETURN 'N';
222 ELSE
223 RETURN 'F';
224 END IF;
225 CLOSE spec_bal;
226 END IF;
227
228 END xlated_ever;
229
230
231 FUNCTION FIRST_EVER_PERIOD_CHECK (x_ledger_id NUMBER, x_period VARCHAR2)
232 RETURN BOOLEAN IS
233 l_first_ever_period NUMBER;
234 l_object_type_code VARCHAR2(1);
235 l_period_set_name VARCHAR2(15);
236 l_period_type VARCHAR2(15);
237
238 l_ledger_id NUMBER;
239 CURSOR first_period IS
240 SELECT 1
241 FROM gl_period_statuses gps
242 WHERE gps.application_id = 101
243 AND gps.ledger_id = l_ledger_id
244 AND EXISTS
245 (SELECT (gp1.period_year*10000+ gp1.period_num)
246 FROM GL_PERIODS gp1
247 WHERE gp1.period_name = x_period
248 AND gp1.period_set_name = l_period_set_name
249 AND gps.effective_period_num <
250 (gp1.period_year*10000+ gp1.period_num))
251 AND ROWNUM = 1;
252
253
254 BEGIN
255 l_first_ever_period := 0;
256
257 -- Find out the the ledger id passed is a ledger or a set.
258
259 SELECT gll.period_set_name,
260 gll.accounted_period_type,
261 gll.object_type_code
262 INTO l_period_set_name,
263 l_period_type,
264 l_object_type_code
265 FROM gl_ledgers gll
266 WHERE ledger_id = x_ledger_id;
267
268 IF (l_object_type_code = 'S') THEN
269
270 SELECT glsa.ledger_id
271 INTO l_ledger_id
272 FROM GL_LEDGER_SET_ASSIGNMENTS glsa,
273 GL_LEDGERS l
274 WHERE glsa.ledger_set_id = x_ledger_id
275 AND glsa.ledger_id = l.ledger_id
276 AND l.object_type_Code = 'L'
277 AND ROWNUM = 1;
278 ELSE
279 l_ledger_id := x_ledger_id;
280 END IF;
281
282 -- Check is the period passed is first defined period in the calendar.
283 -- If there are no periods defined prior to the passed period, then
284 -- the passed period is first defined period.
285
286 OPEN First_period;
287 FETCH First_period INTO l_first_ever_period;
288 IF(First_period%FOUND) THEN
289 RETURN FALSE;
290 ELSE
291 RETURN TRUE;
292 END IF;
293
294 CLOSE First_Period;
295
296
297 END FIRST_EVER_PERIOD_CHECK;
298
299 END GL_FC_XLATE_PKG;
[Home] [Help]