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