[Home] [Help]
PACKAGE BODY: APPS.FUN_GL_TRANSFER
Source
1 PACKAGE BODY FUN_GL_TRANSFER AS
2 /* $Header: FUN_GL_XFER_B.pls 120.13.12010000.3 2008/10/27 08:23:16 makansal ship $ */
3
4 FUNCTION get_conversion_type (
5 p_conversion_type IN VARCHAR2) RETURN VARCHAR2
6 IS
7 l_user_conversion_type GL_DAILY_CONVERSION_TYPES.USER_CONVERSION_TYPE%TYPE;
8 BEGIN
9
10 SELECT USER_CONVERSION_TYPE
11 INTO l_user_conversion_type
12 from GL_DAILY_CONVERSION_TYPES
13 where conversion_type = p_conversion_type;
14
15 return l_user_conversion_type;
16 END get_conversion_type;
17
18 /*-----------------------------------------------------
19 * FUNCTION lock_and_transfer
20 * ----------------------------------------------------
21 * Acquires lock and transfer.
22 * ---------------------------------------------------*/
23
24 FUNCTION lock_and_transfer (
25 p_trx_id IN number,
26 p_ledger_id IN number,
27 p_gl_date IN date,
28 p_currency IN varchar2,
29 p_category IN varchar2,
30 p_source IN varchar2,
31 p_desc IN varchar2,
32 p_conv_date IN date,
33 p_conv_type IN varchar2,
34 p_party_type IN varchar2) RETURN boolean
35 IS
36 l_status varchar2(15);
37 l_desc varchar2(240);
38 l_batch_number varchar2(50);
39 l_batch_id number(15,0);
40 BEGIN
41 IF (NOT lock_transaction(p_trx_id, p_party_type)) THEN
42 RETURN FALSE;
43 ELSE
44 SELECT status, description, batch_id
45 INTO l_status, l_desc, l_batch_id
46 FROM fun_trx_headers
47 WHERE trx_id = p_trx_id;
48
49 -- Added so logic to get batch number required as reference4 in gl_interface
50
51 select batch_number
52 INTO l_batch_number
53 from fun_trx_batches
54 Where batch_id = l_batch_id;
55
56 transfer_single(l_batch_number,p_trx_id, p_ledger_id, p_gl_date, p_currency,
57 p_category, p_source, nvl(l_desc, p_desc), p_conv_date,
58 p_conv_type, p_party_type);
59
60 l_status := update_status(p_trx_id, l_status, p_party_type);
61 END IF;
62
63 RETURN TRUE;
64 END lock_and_transfer;
65
66
67
68 /*-----------------------------------------------------
69 * FUNCTION lock_transaction
70 * ----------------------------------------------------
71 * Lock the transaction.
72 * If p_status is not null, test if it's valid still.
73 * ---------------------------------------------------*/
74
75 FUNCTION lock_transaction (
76 p_trx_id IN number,
77 p_party_type IN varchar2) RETURN boolean
78 IS
79 l_status varchar2(15);
80 BEGIN
81 SELECT status INTO l_status
82 FROM fun_trx_headers
83 WHERE trx_id = p_trx_id
84 FOR UPDATE;
85
86 IF (l_status = 'APPROVED' OR
87 (p_party_type = 'I' AND l_status = 'XFER_RECI_GL') OR
88 (p_party_type = 'R' AND l_status = 'XFER_INI_GL')) THEN
89 RETURN TRUE;
90 ELSE
91 RETURN FALSE;
92 END IF;
93 END lock_transaction;
94
95
96
97 /*-----------------------------------------------------
98 * FUNCTION has_conversion_rate
99 * ----------------------------------------------------
100 * Is there a conversion rate between the two
101 * currencies?
102 * ---------------------------------------------------*/
103
104 FUNCTION has_conversion_rate (
105 p_from_currency IN varchar2,
106 p_to_currency IN varchar2,
107 p_exchange_type IN varchar2,
108 p_exchange_date IN date) RETURN number
109 IS
110 l_rate number;
111 BEGIN
112 IF (p_from_currency = p_to_currency) THEN
113 RETURN 1;
114 END IF;
115
116 /*REPLACED BY GL_CURRENCY_API
117 SELECT COUNT(conversion_rate) INTO l_has_rate
118 FROM gl_daily_rates
119 WHERE from_currency = p_from_currency AND
120 to_currency = p_to_currency AND
121 conversion_type = p_exchange_type AND
122 conversion_date = p_exchange_date;
123
124 IF (l_has_rate = 0) THEN
125 RETURN FALSE;
126 END IF;
127 RETURN TRUE;
128 */
129 l_rate := GL_CURRENCY_API.Get_Rate_Sql(p_from_currency, p_to_currency,
130 p_exchange_date, p_exchange_type);
131
132 return l_rate;
133
134 END has_conversion_rate;
135
136
137
138 /*-----------------------------------------------------
139 * FUNCTION get_period_status
140 * ----------------------------------------------------
141 * Returns the period closing status.
142 * ---------------------------------------------------*/
143
144 FUNCTION get_period_status (
145 p_app_id IN number,
146 p_date IN date,
147 p_ledger_id IN number) RETURN varchar2
148 IS
149 l_status varchar2(1);
150 BEGIN
151 /*SELECT ps.closing_status
152 INTO l_status
153 FROM gl_periods p,
154 gl_ledgers l,
155 gl_period_statuses ps
156 WHERE l.ledger_id = p_ledger_id AND
157 p.period_set_name = l.period_set_name AND
158 p_date BETWEEN p.start_date AND p.end_date AND
159 ps.period_name = p.period_name AND
160 ps.application_id = p_app_id AND
161 ps.set_of_books_id = l.ledger_id;*/
162
163 /* Bug 6707980 added ps.adjustment_period_flag = 'N' where claus */
164
165 SELECT ps.closing_status
166 INTO l_status
167 FROM gl_period_statuses ps
168 WHERE ps.ledger_id = p_ledger_id AND
169 p_date BETWEEN ps.start_date AND ps.end_date AND
170 ps.application_id = p_app_id AND
171 ps.adjustment_period_flag = 'N';
172
173 RETURN l_status;
174 END get_period_status;
175
176
177
178 /*-----------------------------------------------------
179 * FUNCTION update_status
180 * ----------------------------------------------------
181 * Returns the new status.
182 * ---------------------------------------------------*/
183
184 FUNCTION update_status (
185 p_trx_id IN number,
186 p_status IN varchar2,
187 p_party_type IN varchar2) RETURN varchar2
188 IS
189 l_result varchar2(1);
190 l_msg_count number;
191 l_msg_data varchar2(1000);
192 BEGIN
193 IF (p_status = 'APPROVED' AND p_party_type = 'R') THEN
194 fun_trx_pvt.update_trx_status
195 (p_api_version => 1.0,
196 x_return_status => l_result,
197 x_msg_count => l_msg_count,
198 x_msg_data => l_msg_data,
199 p_trx_id => p_trx_id,
200 p_update_status_to => 'XFER_RECI_GL');
201 RETURN 'XFER_RECI_GL';
202 ELSIF (p_status = 'APPROVED' AND p_party_type = 'I') THEN
203 fun_trx_pvt.update_trx_status
204 (p_api_version => 1.0,
205 x_return_status => l_result,
206 x_msg_count => l_msg_count,
207 x_msg_data => l_msg_data,
208 p_trx_id => p_trx_id,
209 p_update_status_to => 'XFER_INI_GL');
210 RETURN 'XFER_INI_GL';
211 ELSIF (p_status = 'XFER_INI_GL') THEN
212 fun_trx_pvt.update_trx_status
213 (p_api_version => 1.0,
214 x_return_status => l_result,
215 x_msg_count => l_msg_count,
216 x_msg_data => l_msg_data,
217 p_trx_id => p_trx_id,
218 p_update_status_to => 'COMPLETE');
219 RETURN 'COMPLETE';
220 ELSIF (p_status = 'XFER_RECI_GL') THEN
221 fun_trx_pvt.update_trx_status
222 (p_api_version => 1.0,
223 x_return_status => l_result,
224 x_msg_count => l_msg_count,
225 x_msg_data => l_msg_data,
226 p_trx_id => p_trx_id,
227 p_update_status_to => 'COMPLETE');
228 RETURN 'COMPLETE';
229 ELSE
230 RAISE corrupted_transaction_status;
231 END IF;
232 END update_status;
233
234
235
236
237 /*-----------------------------------------------------
238 * PROCEDURE transfer_single
239 * ----------------------------------------------------
240 * Transfer a single transaction to GL interface.
241 * It assumes that the caller has a lock on the
242 * transaction, and will do the commit.
243 * ---------------------------------------------------*/
244
245 PROCEDURE transfer_single (
246 p_batch_number IN varchar2,
247 p_trx_id IN number,
248 p_ledger_id IN number,
249 p_gl_date IN date,
250 p_currency IN varchar2,
251 p_category IN varchar2,
252 p_source IN varchar2,
253 p_desc IN varchar2,
254 p_conv_date IN date,
255 p_conv_type IN varchar2,
256 p_party_type IN varchar2)
257 IS
258 l_amount_cr number;
259 l_amount_dr number;
260 l_ccid number;
261 l_created_by number;
262 l_line_id number;
263 l_desc varchar2(240);
264 l_batch_id number;
265 l_trx_id number;
266 l_dist_id number;
267 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
268 l_event_key VARCHAR2(240);
269 l_initdate date;
270 l_trans_source gl_je_sources_tl.user_je_source_name%TYPE;
271 l_trans_category gl_je_categories_tl.user_je_category_name%TYPE;
272
273
274 CURSOR c_dist IS
275 SELECT d.amount_cr, d.amount_dr, d.ccid,
276 d.created_by, d.line_id, d.description,
277 h.batch_id,
278 h.trx_id,
279 d.dist_id
280 FROM fun_dist_lines d,
281 fun_trx_lines t,
282 fun_trx_headers h
283 WHERE t.trx_id = p_trx_id AND
284 d.line_id = t.line_id AND
285 h.trx_id = t.trx_id AND
286 d.party_type_flag = p_party_type;
287 BEGIN
288 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
289 p_value=>TO_CHAR(p_trx_id),
290 p_parameterlist =>l_parameter_list
291 );
292 select sysdate into l_initdate from dual;
293
294 WF_EVENT.AddParameterToList(p_name=>'INIT_SYS_DATE',
295 p_value=>TO_CHAR(l_initdate),
296 p_parameterlist=>l_parameter_list
297 );
298
299 WF_EVENT.AddParameterToList(p_name=>'TRX_TYPE',
300 p_value=>'Intercompany Transaction',
301 p_parameterlist=>l_parameter_list
302 );
303
304 /* made changes for 7350856 */
305 select user_je_source_name into l_trans_source from gl_je_sources_tl where
306 je_source_name = p_source and language = USERENV('LANG');
307
308 select user_je_category_name into l_trans_category from gl_je_categories_tl where
309 je_category_name = p_category and language = USERENV('LANG');
310 /* changes for 7350856 ends */
311
312 OPEN c_dist;
313
314 LOOP
315 FETCH c_dist INTO l_amount_cr, l_amount_dr, l_ccid,
316 l_created_by, l_line_id, l_desc,
317 l_batch_id, l_trx_id, l_dist_id;
318 EXIT WHEN c_dist%NOTFOUND;
319
320 INSERT INTO gl_interface
321 (status, set_of_books_id, accounting_date,
322 currency_code, date_created, created_by,
323 actual_flag, user_je_category_name, user_je_source_name,
324 currency_conversion_date, user_currency_conversion_type, entered_dr,
325 entered_cr, reference10,reference4,
326 code_combination_id, group_id,ledger_id,
327 reference21, reference22, reference23, reference24, reference25)
328 VALUES
329 ('NEW', p_ledger_id, p_gl_date,
330 p_currency, SYSDATE, l_created_by,
331 'A', l_trans_category, l_trans_source,
332 p_conv_date, FUN_GL_TRANSFER.GET_CONVERSION_TYPE(p_conv_type), l_amount_dr,
333 l_amount_cr, nvl(l_desc,p_desc),p_batch_number,
334 l_ccid, p_ledger_id ,p_ledger_id,
335 'Intercompany Transaction',
336 l_batch_id,
337 l_trx_id,
338 l_line_id,
339 l_dist_id
340 );
341 END LOOP;
342
343 l_event_key:=FUN_INITIATOR_WF_PKG.GENERATE_KEY(p_batch_id=>l_batch_id,
344 p_trx_id =>p_trx_id);
345
346 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.single.gl.transfer',
347 p_event_key =>l_event_key,
348 p_parameters =>l_parameter_list);
349 l_parameter_list.delete();
350
351
352 END transfer_single;
353
354
355
356 PROCEDURE transfer_batch (
357 p_request_id IN number,
358 p_source IN varchar2,
359 p_category IN varchar2,
360 p_date_low IN date DEFAULT NULL,
361 p_date_high IN date DEFAULT NULL,
362 p_ledger_low IN varchar2 DEFAULT NULL,
363 p_ledger_high IN varchar2 DEFAULT NULL,
364 p_le_low IN varchar2 DEFAULT NULL,
365 p_le_high IN varchar2 DEFAULT NULL,
366 p_ic_org_low IN varchar2 DEFAULT NULL,
367 p_ic_org_high IN varchar2 DEFAULT NULL,
368 p_commit_freq IN number DEFAULT 100)
369 IS
370 BEGIN
371 --
372 -- This procedure is obsolete and should never be called
373 -- Batch process has been written as a speperate conc program.
374
375 NULL;
376
377 END transfer_batch;
378
379 END;