[Home] [Help]
PACKAGE BODY: APPS.FARX_TF
Source
1 PACKAGE BODY FARX_TF as
2 /* $Header: farxtfb.pls 120.6 2005/08/22 15:04:06 dfred ship $ */
3
4 procedure transfers (
5 book varchar2,
6 begin_period varchar2,
7 end_period varchar2,
8 request_id number,
9 user_id number,
10 retcode out nocopy number,
11 errbuf out nocopy varchar2) is
12
13 h_count number;
14 h_request_id number;
15 h_login_id number;
16
17 h_serial_number varchar2(35);
18 h_tag_number varchar2(25);
19 h_asset_number varchar2(25);
20 h_description varchar2(80);
21 h_to_ccid number;
22 h_from_ccid number;
23 h_cost_acct varchar2(25);
24 h_reserve_acct varchar2(25);
25 h_ytd_deprn number;
26 h_reserve number;
27 h_thid number;
28 h_to_from_flag varchar2(1);
29 h_to_location_id number;
30 h_from_location_id number;
31 h_to_emp_name varchar2(240);
32 h_to_emp_number varchar2(30);
33 h_from_emp_name varchar2(240);
34 h_from_emp_number varchar2(30);
35 h_trx_date date;
36 h_tfr_cost number;
37 h_tfr_reserve number;
38 h_tfr_units number;
39 h_mass_ref_id number;
40 h_inventorial varchar2(3);
41
42 h_concat_to_loc varchar2(500);
43 h_to_loc_segs fa_rx_shared_pkg.Seg_Array;
44 h_concat_from_loc varchar2(500);
45 h_from_loc_segs fa_rx_shared_pkg.Seg_Array;
46
47 h_concat_to_acct varchar2(500);
48 h_to_acct_segs fa_rx_shared_pkg.Seg_Array;
49 h_concat_from_acct varchar2(500);
50 h_from_acct_segs fa_rx_shared_pkg.Seg_Array;
51 h_bal_seg number;
52 h_cc_seg number;
53 h_acct_seg number;
54
55 h_acct_structure number;
56 h_loc_structure number;
57
58 h_period1_pod date;
59 h_period2_pcd date;
60 h_book varchar2(15);
61
62 h_asset_type varchar2(15);
63
64 h_mesg_name varchar2(50);
65 h_mesg_str varchar2(2000);
66 h_flex_error varchar2(5);
67 h_ccid_error number;
68 h_distid number;
69 h_asset number;
70
71 cursor transfer_lines is
72 SELECT
73 ad.asset_number, ad.description,
74 ad.serial_number, ad.tag_number, ad.inventorial,
75 cb.deprn_reserve_acct,
76 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
77 CB.ASSET_COST_ACCT) ,
78 -- dd.ytd_deprn, dd.deprn_reserve,
79 TH.TRANSACTION_HEADER_ID, th.mass_reference_id,
80 tdh.distribution_id,
81 tascc.code_combination_id, fascc.code_combination_id,
82 tloc.location_id, floc.location_id,
83 temp.full_name, temp.employee_number,
84 femp.full_name, femp.employee_number,
85 TH.TRANSACTION_DATE_ENTERED,
86 ad.asset_type,
87 SUM(CADJ.ADJUSTMENT_AMOUNT*
88 DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)),
89 SUM(TDH.UNITS_ASSIGNED)
90 FROM
91 fa_category_books cb,
92 fa_asset_history ah,
93 FA_LOCATIONS TLOC, FA_LOCATIONS FLOC,
94 PER_ALL_PEOPLE_F Temp,
95 PER_ALL_PEOPLE_F FEMP,
96 FA_ADDITIONS AD,
97 GL_CODE_COMBINATIONS TASCC, GL_CODE_COMBINATIONS FASCC,
98 -- fa_deprn_detail dd,
99 FA_DISTRIBUTION_HISTORY TDH, FA_DISTRIBUTION_HISTORY FDH,
100 FA_TRANSACTION_HEADERS TH,
101 FA_ADJUSTMENTS CADJ
102 WHERE
103 AH.ASSET_ID = TH.ASSET_ID AND
104 TH.DATE_EFFECTIVE >= AH.DATE_EFFECTIVE AND
105 TH.DATE_EFFECTIVE < NVL(AH.DATE_INEFFECTIVE,SYSDATE)
106 AND
107 CB.CATEGORY_ID = AH.CATEGORY_ID AND
108 CB.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
109 AND
110 TH.BOOK_TYPE_CODE = h_book AND
111 TH.TRANSACTION_TYPE_CODE = 'TRANSFER' AND
112 TH.DATE_EFFECTIVE >= H_PERIOD1_POD AND
113 TH.DATE_EFFECTIVE <= nvl(H_PERIOD2_PCD,sysdate)
114 AND
115 TH.TRANSACTION_HEADER_ID = TDH.TRANSACTION_HEADER_ID_IN AND
116 TH.TRANSACTION_HEADER_ID = FDH.TRANSACTION_HEADER_ID_OUT
117 AND
118 AD.ASSET_ID = TH.ASSET_ID
119 --AND dd.asset_id = dh.asset_id and
120 -- dd.book_type_code = dh.book_type_code and
121 -- dd.distribution_id = dh.distribution_id and
122 -- dd.period_counter = (select max(dd1.period_counter) from
123 -- fa_deprn_detail dd1 where
124 -- dd1.distribution_id = dd.distribution_id
125 -- and dd1.book_type_code = dd.book_type_code
126 -- and dd1.asset_id = dd.asset_id)
127 AND
128 FLOC.LOCATION_ID = FDH.LOCATION_ID AND
129 TLOC.LOCATION_ID = TDH.LOCATION_ID
130 AND
131 Temp.person_id (+) = Tdh.assigned_to
132 AND TRUNC(SYSDATE) BETWEEN TEMP.EFFECTIVE_START_DATE(+) AND TEMP.EFFECTIVE_END_DATE(+)
133 AND FEMP.PERSON_ID (+) = FDH.ASSIGNED_TO
134 AND TRUNC(SYSDATE) BETWEEN FEMP.EFFECTIVE_START_DATE(+) AND FEMP.EFFECTIVE_END_DATE(+)
135 AND
136 TASCC.CODE_COMBINATION_ID = TDH.CODE_COMBINATION_ID AND
137 FASCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
138 AND
139 CADJ.BOOK_TYPE_CODE = h_book AND
140 CADJ.ASSET_ID = TH.ASSET_ID AND
141 CADJ.DISTRIBUTION_ID = TDH.DISTRIBUTION_ID AND
142 CADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
143 CADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
144 CADJ.ADJUSTMENT_TYPE in ('COST','CIP COST')
145 GROUP BY
146 ad.asset_number, ad.description,
147 ad.serial_number, ad.tag_number, ad.inventorial,
148 cb.deprn_reserve_acct,
149 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
150 CB.ASSET_COST_ACCT) ,
151 -- dd.ytd_deprn, dd.deprn_reserve,
152 TH.TRANSACTION_HEADER_ID,th.mass_reference_id,
153 TDH.DISTRIBUTION_ID,
154 tascc.code_combination_id, fascc.code_combination_id,
155 tloc.location_id, floc.location_id,
156 temp.full_name, temp.employee_number,
157 femp.full_name, femp.employee_number,
158 AD.ASSET_NUMBER, ad.description, ad.serial_number, ad.tag_number,
159 TH.TRANSACTION_DATE_ENTERED,
160 AD.asset_type;
161
162 cursor c_reserve is
163 SELECT
164 ad.asset_number,
165 cb.deprn_reserve_acct,
166 SUM(NVL(RADJ.ADJUSTMENT_AMOUNT,0) *
167 DECODE(NVL(RADJ.DEBIT_CREDIT_FLAG,'CR'),'CR',1,'DR',-1) )
168 FROM
169 fa_category_books cb,
170 FA_ADDITIONS AD,
171 FA_ASSET_HISTORY AH,
172 FA_TRANSACTION_HEADERS TH,
173 FA_ADJUSTMENTS RADJ
174 WHERE
175 AD.ASSET_ID = TH.ASSET_ID AND
176 TH.ASSET_ID = AH.ASSET_ID
177 AND
178 TH.DATE_EFFECTIVE >= AH.DATE_EFFECTIVE AND
179 TH.DATE_EFFECTIVE < NVL(AH.DATE_INEFFECTIVE,SYSDATE)
180 AND
181 CB.CATEGORY_ID = AH.CATEGORY_ID AND
182 CB.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
183 AND
184 TH.BOOK_TYPE_CODE = h_book AND
185 TH.TRANSACTION_TYPE_CODE = 'TRANSFER' AND
186 TH.DATE_EFFECTIVE >= H_PERIOD1_POD AND
187 TH.DATE_EFFECTIVE <= nvl(H_PERIOD2_PCD,sysdate)
188 AND
189 RADJ.BOOK_TYPE_CODE = h_book AND
190 RADJ.ASSET_ID = TH.ASSET_ID AND
191 radj.distribution_id = h_distid and
192 RADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
193 RADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
194 RADJ.ADJUSTMENT_TYPE = 'RESERVE'
195 GROUP BY
196 ad.asset_number,
197 cb.deprn_reserve_acct;
198
199
200 begin
201 h_book := book;
202 h_request_id := request_id;
203
204 select fcr.last_update_login into h_login_id
205 from fnd_concurrent_requests fcr
206 where fcr.request_id = h_request_id;
207
208 h_mesg_name := 'FA_AMT_SEL_PERIODS';
209
210 select period_open_date
211 into h_period1_pod
212 from fa_deprn_periods
213 where book_type_code = h_book and period_name = begin_period;
214
215 select count(*) into h_count
216 from fa_deprn_periods where period_name = end_period
217 and book_type_code = h_book;
218
219 if (h_count > 0) then
220 select period_close_date
221 into h_period2_pcd
222 from fa_deprn_periods
223 where book_type_code = h_book and period_name = end_period;
224 else
225 h_period2_pcd := null;
226 end if;
227
228 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
229
230 select location_flex_structure
231 into h_loc_structure
232 from fa_system_controls;
233
234 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
235
236 select accounting_flex_structure
237 into h_acct_structure
238 from fa_book_controls
239 where book_type_code = h_book;
240
241 h_mesg_name := 'FA_RX_SEGNUMS';
242
243 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
244 BOOK => h_book,
245 BALANCING_SEGNUM => h_bal_seg,
246 ACCOUNT_SEGNUM => h_acct_seg,
247 CC_SEGNUM => h_cc_seg,
248 CALLING_FN => 'ADD_BY_PERIOD');
249
250 h_count := 0;
251
252 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
253
254 open transfer_lines;
255 loop
256
257 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
258
259 fetch transfer_lines into
260 h_asset_number,
261 h_description,
262 h_serial_number,
263 h_tag_number, h_inventorial,
264 h_reserve_acct,
265 h_cost_acct,
266 -- h_ytd_deprn,
267 -- h_reserve,
268 h_thid,
269 h_mass_ref_id,
270 h_distid,
271 h_to_ccid, h_from_ccid,
272 h_to_location_id, h_from_location_id,
273 h_to_emp_name, h_to_emp_number,
274 h_from_emp_name, h_from_emp_number,
275 h_trx_date,
276 h_asset_type,
277 h_tfr_cost,
278 h_tfr_units;
279
280 if (transfer_lines%NOTFOUND) then exit; end if;
281 h_count := h_count + 1;
282
283 h_mesg_name := 'FA_RX_CONCAT_SEGS';
284 h_flex_error := 'GL#';
285 h_ccid_error := h_to_ccid;
286
287 fa_rx_shared_pkg.concat_acct (
288 struct_id => h_acct_structure,
289 ccid => h_to_ccid,
290 concat_string => h_concat_to_acct,
291 segarray => h_to_acct_segs);
292
293 h_flex_error := 'LOC#';
294 h_ccid_error := h_to_location_id;
295
296 fa_rx_shared_pkg.concat_location (
297 struct_id => h_loc_structure,
298 ccid => h_to_location_id,
299 concat_string => h_concat_to_loc,
300 segarray => h_to_loc_segs);
301
302 h_flex_error := 'GL#';
303 h_ccid_error := h_from_ccid;
304
305 fa_rx_shared_pkg.concat_acct (
306 struct_id => h_acct_structure,
307 ccid => h_from_ccid,
308 concat_string => h_concat_from_acct,
309 segarray => h_from_acct_segs);
310
311 h_flex_error := 'LOC#';
312 h_ccid_error := h_from_location_id;
313
314 fa_rx_shared_pkg.concat_location (
315 struct_id => h_loc_structure,
316 ccid => h_from_location_id,
317 concat_string => h_concat_from_loc,
318 segarray => h_from_loc_segs);
319
320
321 open c_reserve;
322 fetch c_reserve into h_asset, h_reserve_acct, h_tfr_reserve;
323 close c_reserve;
324
325
326 insert into fa_transfer_rep_itf (
327 request_id, asset_number, description, serial_number, tag_number,
328 to_company, to_cost_center, to_expense_acct,
329 from_company, from_cost_center, from_expense_acct,
330 reserve_acct, cost_acct, inventorial,
331 transaction_header_id, mass_transfer_id, to_location, from_location,
332 transaction_date, to_employee_name, to_employee_number,
333 from_employee_name, from_employee_number,
334 cost_transferred, reserve_transferred, units_transferred,
335 created_by, creation_date, last_updated_by, last_update_date,
336 last_update_login, asset_type)
337 values (request_id, h_asset_number, h_description, h_serial_number,
338 h_tag_number,
339 h_to_acct_segs(h_bal_seg), h_to_acct_segs(h_cc_seg),
340 h_to_acct_segs(h_acct_seg), h_from_acct_segs(h_bal_seg),
341 h_from_acct_segs(h_cc_seg), h_from_acct_segs(h_acct_seg),
342 h_reserve_acct, h_cost_acct, h_inventorial, h_thid, h_mass_ref_id,
343 h_concat_to_loc, h_concat_from_loc, h_trx_date,
344 h_to_emp_name, h_to_emp_number, h_from_emp_name, h_from_emp_number,
345 h_tfr_cost, h_tfr_reserve, h_tfr_units,
346 user_id, sysdate, user_id, sysdate, h_login_id, h_asset_type);
347
348
349
350
351 end loop;
352
353 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
354
355 close transfer_lines;
356
357 exception when others then
358 if SQLCODE <> 0 then
359 fa_Rx_conc_mesg_pkg.log(SQLERRM);
360 end if;
361
362 fnd_message.set_name('OFA',h_mesg_name);
363 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
364 fnd_message.set_token('TABLE','FA_TRANSFER_REP_ITF',FALSE);
365 end if;
366 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
367 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
368 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
369 end if;
370
371 h_mesg_str := fnd_message.get;
372 fa_rx_conc_mesg_pkg.log(h_mesg_str);
373 retcode := 2;
374
375 end transfers;
376
377 END FARX_TF;