[Home] [Help]
PACKAGE BODY: APPS.FARX_TAX_PKG
Source
1 PACKAGE BODY FARX_TAX_PKG as
2 /* $Header: farxptb.pls 115.10 2004/04/29 12:47:27 skchawla ship $ */
3
4 procedure property_tax(
5 book in varchar2,
6 end_date in date,
7 segment1 in varchar2,
8 segment2 in varchar2,
9 segment3 in varchar2,
10 segment4 in varchar2,
11 segment5 in varchar2,
12 segment6 in varchar2,
13 segment7 in varchar2,
14 property_type in varchar2,
15 company in varchar2,
16 cost_center in varchar2,
17 cost_account in varchar2,
18 request_id in number,
19 user_id in number,
20 retcode out nocopy varchar2,
21 errbuf out nocopy varchar2) is
22
23 h_login_id number;
24 h_asset_number varchar2(25);
25 h_serial_number varchar2(35);
26 h_tag_number varchar2(15);
27 h_description varchar2(80);
28 h_inventorial varchar2(3);
29
30 h_concat_loc varchar2(500);
31 h_concat_cat varchar2(500);
32 h_concat_key varchar2(500);
33 h_loc_segs fa_rx_shared_pkg.Seg_Array;
34 h_cat_segs fa_rx_shared_pkg.Seg_Array;
35 h_key_segs fa_rx_shared_pkg.Seg_Array;
36
37 h_state_loc varchar2(50);
38
39 h_state_seg number;
40
41 h_concat_acct varchar2(500);
42 h_acct_segs fa_rx_shared_pkg.Seg_Array;
43
44 h_bal_seg number;
45 h_cc_seg number;
46 h_acct_seg number;
47
48 h_acct_structure number;
49 h_loc_structure number;
50 h_cat_structure number;
51 h_key_structure number;
52
53 h_book varchar2(15);
54 h_state varchar2(30);
55 h_end_date date;
56 h_segment1 varchar2(30);
57 h_segment2 varchar2(30);
58 h_segment3 varchar2(30);
59 h_segment4 varchar2(30);
60 h_segment5 varchar2(30);
61 h_segment6 varchar2(30);
62 h_segment7 varchar2(30);
63 h_property_type varchar2(10);
64 h_cost_center varchar2(30);
65 h_company varchar2(30);
66 h_cost_account varchar2(25);
67
68 h_period_ctr number;
69 h_request_id number;
70 h_segment_num number;
71
72 Cursor c1 is
73 SELECT bc.book_type_code,
74 bc.accounting_flex_structure,
75 sob.currency_code,
76 cur.precision,
77 dp.period_counter
78 FROM fa_book_controls bc,
79 gl_sets_of_books sob,
80 fnd_currencies cur,
81 fa_deprn_periods dp
82 WHERE bc.book_type_code = h_book
83 AND bc.date_ineffective is null
84 AND sob.set_of_books_id = bc.set_of_books_id
85 AND sob.currency_code = cur.currency_code
86 AND dp.book_Type_code = h_book
87 AND h_end_Date between dp.period_open_date and
88 nvl(dp.period_close_date,h_end_date+1);
89 c1rec c1%rowtype;
90
91 Cursor c_main is
92 SELECT lo.location_id,
93 comcc.code_combination_id, /* comcc.segment1 company, */
94 bk.book_type_code,
95 cb.asset_cost_acct,
96 to_char(bk.date_placed_in_service, 'YYYY') year,
97 ad.asset_number,
98 ad.tag_number, ad.serial_number,
99 ad.description, ad.inventorial,
100 lpad(to_char(sum(di.units_assigned)),6) units,
101 bk.date_placed_in_service ,
102 round(sum(bk.cost *
103 (di.units_assigned/ah.units/*ad.current_units bugfix 3591282*/)),c1rec.precision) cost,
104 lo.segment1,
105 lo.segment2,
106 lo.segment3,
107 lo.segment4,
108 lo.segment5,
109 lo.segment6,
110 lo.segment7,
111 fc.category_id,
112 ad.property_type_code,
113 ad.asset_key_ccid
114 FROM fa_system_controls sc,
115 fa_locations lo,
116 fa_books bk,
117 fa_asset_history ah,
118 fa_additions ad,
119 fa_distribution_history di,
120 gl_Code_combinations comcc,
121 fa_category_books cb,
122 fa_book_controls bc,
123 fa_categories fc
124 WHERE
125 bk.book_type_code = h_book
126 AND bk.asset_id = ad.asset_id
127 AND ah.asset_id = ad.asset_id
128 AND ah.category_id = cb.category_id
129 AND cb.book_type_code= bk.book_type_code
130 AND bc.book_type_code = bk.book_type_code
131 and di.book_type_code = bc.distribution_source_book
132 AND di.location_id = lo.location_id
133 AND di.asset_id = bk.asset_id
134 AND di.code_combination_id= comcc.code_combination_id
135 AND ad.asset_category_id = fc.category_id
136 AND cb.asset_cost_acct= nvl(h_cost_account, cb.asset_cost_acct)
137 AND nvl(bk.period_counter_fully_retired, c1rec.period_counter+1) >= c1rec.period_counter
138 AND nvl(lo.segment1,0) = nvl(h_segment1, nvl(lo.segment1, 0))
139 AND nvl(lo.segment2,0) = nvl(h_segment2, nvl(lo.segment2, 0))
140 AND nvl(lo.segment3,0) = nvl(h_segment3, nvl(lo.segment3, 0))
141 AND nvl(lo.segment4,0) = nvl(h_segment4, nvl(lo.segment4, 0))
142 AND nvl(lo.segment5,0) = nvl(h_segment5, nvl(lo.segment5, 0))
143 AND nvl(lo.segment6,0) = nvl(h_segment6, nvl(lo.segment6, 0))
144 AND nvl(lo.segment7,0) = nvl(h_segment7, nvl(lo.segment7, 0))
145 AND ad.property_type_code = nvl(h_property_type, ad.property_type_code)
146 AND ad.owned_leased = 'OWNED'
147 AND h_end_date between bk.date_effective and nvl(bk.date_ineffective,sysdate)
148 AND h_end_date between ah.date_effective and nvl(ah.date_ineffective,sysdate)
149 AND di.transaction_header_id_in <=
150 (select max(th.transaction_header_id)
151 from fa_transaction_headers th
152 where th.asset_id = di.asset_id
153 AND th.book_type_code = di.book_type_code
154 AND th.date_effective <= h_end_date)
155 AND (di.transaction_header_id_out >=
156 (select min(th.transaction_header_id)
157 from fa_transaction_headers th
158 where th.asset_id = di.asset_id
159 AND th.book_type_code = di.book_type_code
160 AND th.date_effective > h_end_date)
161 OR di.transaction_header_id_out is null)
162 GROUP BY
163 bk.book_type_code,
164 comcc.code_combination_id,
165 lo.location_id,
166 lo.segment1,
167 lo.segment2,
168 lo.segment3,
169 lo.segment4,
170 lo.segment5,
171 lo.segment6,
172 lo.segment7,
173 to_char(bk.date_placed_in_service, 'YYYY'),
174 ad.property_type_code,
175 cb.asset_cost_acct,
176 fc.category_id,
177 ad.asset_number, ad.tag_number, ad.serial_number,
178 ad.description, ad.inventorial, ad.asset_key_ccid,
179 bk.date_placed_in_service
180 ORDER BY
181 comcc.code_combination_id,
182 lo.location_id,
183 cb.asset_cost_acct,
184 to_char(bk.date_placed_in_service, 'YYYY'),
185 asset_number;
186 c_mainrec c_main%rowtype;
187
188
189 begin
190
191 h_book := book;
192 h_end_date := end_date;
193 h_request_id := request_id;
194 h_segment1 := segment1;
195 h_segment2 := segment2;
196 h_segment3 := segment3;
197 h_segment4 := segment4;
198 h_segment5 := segment5;
199 h_segment6 := segment6;
200 h_segment7 := segment7;
201 h_property_type := property_type;
202 h_company := company;
203 h_cost_center := cost_center;
204 h_cost_account := cost_account;
205
206
207 --dbms_output.enable (50000);
208
209 select accounting_flex_structure, location_flex_structure, category_flex_structure, asset_key_flex_structure
210 into h_acct_structure, h_loc_structure, h_cat_structure, h_key_structure
211 from fa_book_controls bc, fa_system_controls
212 where book_type_code = h_book;
213
214
215 select fcr.last_update_login
216 into h_login_id
217 from fnd_concurrent_requests fcr
218 where fcr.request_id = h_request_id;
219
220 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
221 BOOK => h_book,
222 BALANCING_SEGNUM => h_bal_seg,
223 ACCOUNT_SEGNUM => h_acct_seg,
224 CC_SEGNUM => h_cc_seg,
225 CALLING_FN => 'PROPERTY_TAX');
226
227
228 SELECT s.segment_num
229 INTO h_segment_num
230 FROM fnd_id_flex_segments s,
231 fnd_segment_attribute_values sav,
232 fnd_segment_attribute_types sat
233 WHERE s.application_id = 140
234 AND s.id_flex_code = 'LOC#'
235 AND s.id_flex_num = h_loc_structure
236 AND s.enabled_flag = 'Y'
237 AND s.application_column_name = sav.application_column_name
238 AND sav.application_id = 140
239 AND sav.id_flex_code = 'LOC#'
240 AND sav.id_flex_num = h_loc_structure
241 AND sav.attribute_value = 'Y'
242 AND sav.segment_attribute_type = sat.segment_attribute_type
243 AND sat.application_id = 140
244 AND sat.id_flex_code = 'LOC#'
245 AND sat.unique_flag = 'Y'
246 AND sat.segment_attribute_type = 'LOC_STATE';
247
248 SELECT count(segment_num)
249 INTO h_state_seg
250 FROM fnd_id_flex_segments
251 WHERE application_id = 140
252 AND id_flex_code = 'LOC#'
253 AND id_flex_num = 101
254 AND enabled_flag = 'Y'
255 AND segment_num <= h_segment_num;
256
257
258 open c1;
259 fetch c1 into c1rec;
260
261 open c_main;
262 loop
263 fetch c_main into c_mainrec;
264
265 if (c_main%NOTFOUND) then exit; end if;
266
267 fa_rx_shared_pkg.concat_acct (
268 struct_id => h_acct_structure,
269 ccid => c_mainrec.code_combination_id,
270 concat_string => h_concat_acct,
271 segarray => h_acct_segs);
272
273 fa_rx_shared_pkg.concat_location (
274 struct_id => h_loc_structure,
275 ccid => c_mainrec.location_id,
276 concat_string => h_concat_loc,
277 segarray => h_loc_segs);
278
279 fa_rx_shared_pkg.concat_category (
280 struct_id => h_cat_structure,
281 ccid => c_mainrec.category_id,
282 concat_string => h_concat_cat,
283 segarray => h_cat_segs);
284
285
286 if c_mainrec.asset_key_ccid is not null then
287 fa_rx_shared_pkg.concat_asset_key (
288 struct_id => h_key_structure,
289 ccid => c_mainrec.asset_key_ccid,
290 concat_string => h_concat_key,
291 segarray => h_key_segs);
292 else
293 h_concat_key := '';
294 end if;
295
296 -- This restriction should really be in the mainselects where-clause,
297 -- but due to historical limited functionality in pl/sql
298 -- it has to be done like this.
299 -- If you want to improve performance, remove this where
300 -- and define a row that looks like this in the main query:
301 -- lo.segment? = h_state
302 -- The columnname segment? depends on what the qualifying statesegment
303 -- has been defined to (usually segment1).
304
305 --dbms_output.put_line('cost account ' || h_acct_segs(h_acct_seg));
306
307
308 if h_acct_segs(h_bal_seg) = nvl(h_company, h_acct_segs(h_bal_seg)) AND
309 h_acct_segs(h_cc_seg) = nvl(h_cost_center, h_acct_segs(h_cc_seg)) then
310
311 insert into fa_proptax_rep_itf (
312 request_id,
313 book_type_code,
314 location,
315 state ,
316 company,
317 asset_cost_acct,
318 year,
319 date_placed_in_service,
320 asset_number, tag_number, serial_number,
321 description, inventorial,
322 units ,
323 original_cost,
324 cost_center,
325 asset_category,
326 asset_key,
327 property_type,
328 segment1,
329 segment2,
330 segment3,
331 segment4,
332 segment5,
333 segment6,
334 segment7,
335 created_by, creation_date, last_updated_by, last_update_date,
336 last_update_login)
337 values (
338 h_request_id,
339 c_mainrec.book_type_code,
340 h_concat_loc,
341 h_loc_segs(h_state_seg),
342 h_acct_segs(h_bal_seg),
343 c_mainrec.asset_cost_acct,
344 c_mainrec.year,
345 c_mainrec.date_placed_in_service ,
346 c_mainrec.asset_number,
347 c_mainrec.tag_number,
348 c_mainrec.serial_number,
349 c_mainrec.description, c_mainrec.inventorial,
350 c_mainrec.units,
351 c_mainrec.cost,
352 h_acct_segs(h_cc_seg),
353 h_concat_cat,
354 h_concat_key,
355 c_mainrec.property_type_code,
356 c_mainrec.segment1,
357 c_mainrec.segment2,
358 c_mainrec.segment3,
359 c_mainrec.segment4,
360 c_mainrec.segment5,
361 c_mainrec.segment6,
362 c_mainrec.segment7,
363 user_id, sysdate, user_id , sysdate, h_login_id);
364
365 end if;
366
367 end loop;
368 close c_main;
369 close c1;
370
371 errbuf := '';
372
373 end property_tax;
374
375 END FARX_TAX_PKG;