[Home] [Help]
PACKAGE BODY: APPS.JAI_PLSQL_CACHE_PKG
Source
1 PACKAGE BODY JAI_PLSQL_CACHE_PKG AS
2 /* $Header: jai_plsql_cache.plb 120.5 2006/08/24 15:33:39 lgopalsa noship $ */
3
4 /* Read from cache */
5 FUNCTION read_cache
6 (p_org_id IN HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE)
7 RETURN func_curr_details AS
8 l_func_curr_det func_curr_details;
9 BEGIN
10 IF (g_get_func_curr.EXISTS (p_org_id)) THEN
11
12 l_func_curr_det.ledger_id := g_get_func_curr(p_org_id).ledger_id;
13 l_func_curr_det.currency_code := g_get_func_curr(p_org_id).currency_code;
14 l_func_curr_det.chart_of_accounts_id := g_get_func_curr(p_org_id).chart_of_accounts_id;
15 l_func_curr_det.organization_code := g_get_func_curr(p_org_id).organization_code;
16 l_func_curr_det.legal_entity := g_get_func_curr(p_org_id).legal_entity;
17 l_func_curr_det.organization_name := g_get_func_curr(p_org_id).organization_name;
18 l_func_curr_det.minimum_acct_unit := g_get_func_curr(p_org_id).minimum_acct_unit;
19 l_func_curr_det.precision := g_get_func_curr(p_org_id).precision;
20
21
22 End if;
23
24 RETURN l_func_curr_det;
25
26 EXCEPTION
27 WHEN OTHERS THEN
28 --NULL ;
29 fnd_file.put_line(FND_FILE.LOG, ' Error reading cache' || SQLERRM);
30
31 END read_cache;
32
33
34 /* Write from cache */
35
36 PROCEDURE write_cache
37 (p_org_id IN HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE,
38 p_func_curr_det IN func_curr_details
39 ) AS
40
41 BEGIN
42 g_get_func_curr(p_org_id).ledger_id := p_func_curr_det.ledger_id;
43 g_get_func_curr(p_org_id).currency_code := p_func_curr_det.currency_code;
44 g_get_func_curr(p_org_id).chart_of_accounts_id := p_func_curr_det.chart_of_accounts_id;
45 g_get_func_curr(p_org_id).organization_code := p_func_curr_det.organization_code;
46 g_get_func_curr(p_org_id).legal_entity := p_func_curr_det.legal_entity;
47 g_get_func_curr(p_org_id).organization_name := p_func_curr_det.organization_name;
48 g_get_func_curr(p_org_id).minimum_acct_unit := p_func_curr_det.minimum_acct_unit;
49 g_get_func_curr(p_org_id).PRECISION := p_func_curr_det.precision;
50
51 EXCEPTION
52 WHEN OTHERS THEN
53 -- null;
54 fnd_file.put_line(FND_FILE.LOG, ' Error writing cache'|| SQLERRM);
55 END write_cache;
56
57
58 /* Read from db and write into cache */
59
60 FUNCTION read_from_db
61 (p_org_id IN HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE)
62 RETURN func_curr_details AS
63 ln_ledger_id NUMBER;
64 lv_curr_code VARCHAR2(10);
65
66 lc_fetch_org_det get_inv_org%ROWTYPE;
67 lc_fetch_curr_det get_func_curr%ROWTYPE;
68 l_func_curr_det func_curr_details;
69 v_debug varchar2(1);
70 -- Bug 5243532. Added by Lakshmi Gopalsami
71 lc_fetch_curr get_curr_details%ROWTYPE;
72
73 BEGIN
74 v_debug := 'N';
75 OPEN get_inv_org (p_org_id);
76 FETCH get_inv_org INTO lc_fetch_org_det;
77 CLOSE get_inv_org;
78
79 /* Print into util file if v_debug ='Y' */
80
81 If v_debug ='Y' Then
82 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log', ' read from db ' );
83 -- jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log',
84 -- ' Inv org ledger id ' || lc_fetch_org_det.ledger_id);
85 End if;
86
87 --fnd_file.put_line(FND_FILE.LOG, '1. ledger id '
88 --|| lc_fetch_org_det.ledger_id);
89
90 IF lc_fetch_org_det.ledger_id IS NULL THEN
91 OPEN get_OU (p_org_id);
92 FETCH get_OU INTO lc_fetch_org_det;
93 CLOSE get_OU;
94 /* Print into util file if v_debug ='Y' */
95 If v_debug ='Y' Then
96 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log',
97 'OU ledger id ' || lc_fetch_org_det.ledger_id);
98 End if;
99 --fnd_file.put_line(FND_FILE.LOG, ' 3. ledger id '
100 --|| lc_fetch_org_det.ledger_id);
101 END if;
102
103 IF lc_fetch_org_det.ledger_id IS NOT NULL THEN
104 OPEN get_func_curr(lc_fetch_org_det.ledger_id);
105 FETCH get_func_curr INTO lc_fetch_curr_det;
106 CLOSE get_func_curr;
107 --fnd_file.put_line(FND_FILE.LOG, ' 2. curr code '
108 -- || lc_fetch_curr_det.curr_code);
109 /* Bug 5243532. Added by Lakshmi Gopalsami
110 Get the precision and minimum accountable unit
111 */
112 IF lc_fetch_curr_det.curr_code IS NOT NULL THEN
113 OPEN get_curr_details(lc_fetch_curr_det.curr_code);
114 Fetch get_curr_details INTO lc_fetch_curr;
115 CLOSE get_curr_details;
116 END IF;
117 END IF;
118
119 /* Bug 5148770. Changed ln_ledger_id to lc_fetch_org_det.ledger_id */
120
121 IF lc_fetch_curr_det.curr_code IS NOT NULL AND lc_fetch_org_det.ledger_id IS NOT NULL THEN
122 l_func_curr_det.ledger_id := lc_fetch_org_det.ledger_id;
123 l_func_curr_det.currency_code := lc_fetch_curr_det.curr_code;
124 l_func_curr_det.chart_of_accounts_id := lc_fetch_curr_det.coa;
125 l_func_curr_det.organization_code := lc_fetch_org_det.org_code;
126 l_func_curr_det.legal_entity := lc_fetch_org_det.leg_ent;
127 l_func_curr_det.organization_name := lc_fetch_org_det.org_name;
128 -- Bug 5243532. Added by Lakshmi Gopalsami
129 l_func_curr_det.minimum_acct_unit := lc_fetch_curr.minimum_acct_unit;
130 l_func_curr_det.precision :=lc_fetch_curr.precision;
131
132 END IF;
133
134 RETURN l_func_curr_det;
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 -- null;
139 fnd_file.put_line(FND_FILE.LOG, ' Error reading database'|| SQLERRM);
140 END read_from_db;
141
142
143 /* Function which performs reading from cache, if not found
144 read from db and write onto the cache and return the same
145 */
146
147 FUNCTION return_sob_curr
148 (p_org_id IN HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE)
149 RETURN func_curr_details AS
150
151 l_func_curr_det func_curr_details;
152 v_debug varchar2(1);
153 BEGIN
154 v_debug := 'N';
155
156 l_func_curr_det := read_cache(p_org_id);
157
158 -- Read from cache and display .
159
160 /*
161 fnd_file.put_line(FND_FILE.LOG, ' value of org id '
162 || p_org_id);
163 fnd_file.put_line(FND_FILE.LOG, ' Cache values - ledger id '
164 || l_func_curr_det.ledger_id);
165 fnd_file.put_line(FND_FILE.LOG, ' Currency Code '
166 || l_func_curr_det.currency_code);
167 fnd_file.put_line(FND_FILE.LOG, ' Chart of Accounts id '
168 || l_func_curr_det.chart_of_accounts_id);
169 fnd_file.put_line(FND_FILE.LOG, ' Organization code '
170 || l_func_curr_det.organization_code);
171 fnd_file.put_line(FND_FILE.LOG, ' Organization Name '
172 || l_func_curr_det.organization_name);
173 fnd_file.put_line(FND_FILE.LOG, ' Legal Entity'
174 || l_func_curr_det.legal_entity);
175 */
176 /* Print into util file if v_debug ='Y' */
177
178 If v_debug ='Y' Then
179 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log', 'from cache ledger id ' ||l_func_curr_det.ledger_id);
180 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log',
181 'Curr code '|| l_func_curr_det.currency_code);
182 End if;
183
184
185 if l_func_curr_det.ledger_id is null then
186
187 -- Read from db as the details are not available in cache.
188
189 l_func_curr_det := read_from_db(p_org_id);
190 /*
191 fnd_file.put_line(FND_FILE.LOG, ' Inside cache value null - org id '
192 || p_org_id);
193 fnd_file.put_line(FND_FILE.LOG, ' Cache values - ledger id '
194 || l_func_curr_det.ledger_id);
195 fnd_file.put_line(FND_FILE.LOG, ' Currency Code '
196 || l_func_curr_det.currency_code);
197 fnd_file.put_line(FND_FILE.LOG, ' Chart of Accounts id '
198 || l_func_curr_det.chart_of_accounts_id);
199 fnd_file.put_line(FND_FILE.LOG, ' Organization code '
200 || l_func_curr_det.organization_code);
201 fnd_file.put_line(FND_FILE.LOG, ' Organization Name '
202 || l_func_curr_det.organization_name);
203 fnd_file.put_line(FND_FILE.LOG, ' Legal Entity'
204 || l_func_curr_det.legal_entity);
205 */
206 /* Print into util file if v_debug ='Y' */
207
208 If v_debug ='Y' Then
209 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log', 'from db ledger id ' ||l_func_curr_det.ledger_id);
210 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log', 'Curr code '|| l_func_curr_det.currency_code);
211 End if;
212
213 if l_func_curr_det.ledger_id is not null then
214 /*
215 fnd_file.put_line(FND_FILE.LOG, ' Cache values - ledger id '
216 || l_func_curr_det.ledger_id);
217 fnd_file.put_line(FND_FILE.LOG, ' Currency Code '
218 || l_func_curr_det.currency_code);
219 fnd_file.put_line(FND_FILE.LOG, ' Chart of Accounts id '
220 || l_func_curr_det.chart_of_accounts_id);
221 fnd_file.put_line(FND_FILE.LOG, ' Organization code '
222 || l_func_curr_det.organization_code);
223 fnd_file.put_line(FND_FILE.LOG, ' Organization Name '
224 || l_func_curr_det.organization_name);
225 fnd_file.put_line(FND_FILE.LOG, ' Legal Entity'
226 || l_func_curr_det.legal_entity);
227 */
228 /* Print into util file if v_debug ='Y' */
229
230 If v_debug ='Y' Then
231 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log',
232 'db value not null ledger id ' ||l_func_curr_det.ledger_id);
233 jai_cmn_utils_pkg.print_log('JAI_PLSQL_CACHE_PKG.log',
234 'Curr code '|| l_func_curr_det.currency_code);
235 End if;
236 -- Write into cache the values got from db.
237 write_cache(p_org_id, l_func_curr_det);
238 end if; /* l_func_curr_det.ledger_id is not null */
239 end if; /* l_func_curr_det.ledger_id is null */
240
241 RETURN l_func_curr_det;
242
243 END return_sob_curr;
244
245 END JAI_PLSQL_CACHE_PKG;