[Home] [Help]
PACKAGE BODY: APPS.FARX_RC
Source
1 PACKAGE BODY FARX_RC as
2 /* $Header: farxrcb.pls 120.2 2005/02/07 15:37:29 bridgway ship $ */
3
4 procedure reclass (
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
14 h_count number;
15 h_book varchar2(15);
16 h_dist_source_book varchar2(15);
17 h_request_id number;
18 h_login_id number;
19
20 h_acct_segs fa_rx_shared_pkg.Seg_Array;
21 h_fcat_segs fa_rx_shared_pkg.Seg_Array;
22 h_tcat_segs fa_rx_shared_pkg.Seg_Array;
23 h_concat_acct varchar2(500);
24 h_concat_fcat varchar2(500);
25 h_concat_tcat varchar2(500);
26 h_cat_struct number;
27 h_acct_struct number;
28 h_bal_seg number;
29 h_cc_seg number;
30 h_acct_seg number;
31
32 h_ccid number;
33 h_period_name varchar2(15);
34 h_to_cost_acct varchar2(25);
35 h_to_reserve_acct varchar2(25);
36 h_from_cost_acct varchar2(25);
37 h_from_reserve_acct varchar2(25);
38 h_asset_number varchar2(15);
39 h_description varchar2(80);
40 h_tag_number varchar2(15);
41 h_serial_number varchar2(35);
42 h_cost number;
43 h_reserve number;
44 h_thid number;
45 h_to_category_id number;
46 h_from_category_id number;
47 h_inventorial varchar2(3);
48
49 h_mesg_name varchar2(50);
50 h_mesg_str varchar2(2000);
51 h_flex_error varchar2(5);
52 h_ccid_error number;
53
54 cursor reclass_lines is
55 SELECT
56 dhcc.code_combination_id,
57 dp.period_name,
58 decode(FAH.ASSET_TYPE,
59 'CIP', FCB.CIP_COST_ACCT,
60 FCB.ASSET_COST_ACCT),
61 decode(FAH.ASSET_TYPE,
62 'CIP', ' ',
63 FCB.DEPRN_RESERVE_ACCT),
64 fcb.category_id,
65 decode(TAH.ASSET_TYPE, 'CIP', TCB.CIP_COST_ACCT,
66 TCB.ASSET_COST_ACCT),
67 decode(TAH.ASSET_TYPE,
68 'CIP', ' ',
69 TCB.DEPRN_RESERVE_ACCT),
70 tcb.category_id,
71 AD.ASSET_NUMBER, ad.description, ad.tag_number, ad.serial_number,
72 ad.inventorial,
73 sum (DECODE(COST_ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, 'CR', -1) *
74 COST_ADJ.ADJUSTMENT_AMOUNT),
75 sum (DECODE(RES_ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 'CR', 1) *
76 NVL(RES_ADJ.ADJUSTMENT_AMOUNT, 0)),
77 TH.TRANSACTION_HEADER_ID
78 FROM
79 FA_DEPRN_PERIODS DP,
80 FA_DEPRN_PERIODS START_DP,
81 FA_DEPRN_PERIODS END_DP,
82 FA_ADDITIONS AD,
83 GL_CODE_COMBINATIONS DHCC,
84 FA_CATEGORIES FCAT,
85 FA_CATEGORIES TCAT,
86 FA_CATEGORY_BOOKS FCB,
87 FA_CATEGORY_BOOKS TCB,
88 FA_TRANSACTION_HEADERS TH,
89 FA_ADJUSTMENTS COST_ADJ,
90 FA_ADJUSTMENTS RES_ADJ,
91 FA_ASSET_HISTORY FAH,
92 FA_ASSET_HISTORY TAH,
93 -- FA_DISTRIBUTION_HISTORY fDH,
94 fa_distribution_history tdh
95 WHERE
96 START_DP.PERIOD_NAME = begin_period AND
97 END_DP.PERIOD_NAME = end_period AND
98 START_DP.BOOK_TYPE_CODE = UPPER (h_book) AND
99 END_DP.BOOK_TYPE_CODE = START_DP.BOOK_TYPE_CODE
100 AND
101 DP.BOOK_TYPE_CODE = START_DP.BOOK_TYPE_CODE AND
102 DP.PERIOD_COUNTER >= START_DP.PERIOD_COUNTER AND
103 DP.PERIOD_COUNTER <= NVL (END_DP.PERIOD_COUNTER,
104 DP.PERIOD_COUNTER)
105 AND
106 TH.BOOK_TYPE_CODE = upper(h_book) AND
107 TH.TRANSACTION_TYPE_CODE = 'RECLASS' AND
108 TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
109 TH.DATE_EFFECTIVE <= NVL (DP.PERIOD_CLOSE_DATE, SYSDATE)
110 AND
111 AD.ASSET_ID = TH.ASSET_ID
112 AND
113 FAH.ASSET_ID = TH.ASSET_ID AND
114 FAH.DATE_INEFFECTIVE = TH.DATE_EFFECTIVE
115 AND
116 TAH.ASSET_ID = TH.ASSET_ID AND
117 TAH.DATE_EFFECTIVE = TH.DATE_EFFECTIVE
118 AND
119 TCAT.CATEGORY_ID = TAH.CATEGORY_ID
120 AND
121 FCAT.CATEGORY_ID = FAH.CATEGORY_ID
122 AND
123 TCB.BOOK_TYPE_CODE = UPPER (h_book) AND
124 TCB.CATEGORY_ID = TAH.CATEGORY_ID
125 AND
126 FCB.BOOK_TYPE_CODE = UPPER (h_book) AND
127 FCB.CATEGORY_ID = FAH.CATEGORY_ID
128 AND
129 COST_ADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
130 COST_ADJ.BOOK_TYPE_CODE = upper(h_book) AND
131 COST_ADJ.SOURCE_TYPE_CODE = 'RECLASS' AND
132 COST_ADJ.ADJUSTMENT_TYPE in ('COST', 'CIP COST') AND
133 COST_ADJ.PERIOD_COUNTER_CREATED >= START_DP.PERIOD_COUNTER AND
134 COST_ADJ.PERIOD_COUNTER_CREATED <= NVL (END_DP.PERIOD_COUNTER,
135 DP.PERIOD_COUNTER)
136 AND
137 RES_ADJ.TRANSACTION_HEADER_ID (+) = COST_ADJ.TRANSACTION_HEADER_ID AND
138 RES_ADJ.ASSET_ID (+) = COST_ADJ.ASSET_ID AND
139 RES_ADJ.DISTRIBUTION_ID (+) = COST_ADJ.DISTRIBUTION_ID AND
140 RES_ADJ.BOOK_TYPE_CODE (+) = UPPER(h_book) AND
141 RES_ADJ.SOURCE_TYPE_CODE (+) = 'RECLASS' AND
142 RES_ADJ.ADJUSTMENT_TYPE (+) = 'RESERVE' AND
143 RES_ADJ.PERIOD_COUNTER_CREATED =
144 COST_ADJ.PERIOD_COUNTER_CREATED
145 AND
146 tDH.BOOK_TYPE_CODE = h_dist_source_book AND
147 tDH.ASSET_ID = TH.ASSET_ID AND
148 tDH.TRANSACTION_HEADER_ID_IN = NVL(TH.SOURCE_TRANSACTION_HEADER_ID, TH.TRANSACTION_HEADER_ID) AND
149 tDH.DISTRIBUTION_ID = COST_ADJ.DISTRIBUTION_ID
150 AND
151 DHCC.CODE_COMBINATION_ID = tDH.CODE_COMBINATION_ID
152 --AND
153 -- fdh.book_type_code = h_dist_source_book AND
154 -- fdh.asset_id = th.asset_id AND
155 -- fdh.transaction_header_id_out = th.transaction_header_id
156 group by
157 dhcc.code_combination_id,
158 dp.period_name,
159 decode(FAH.ASSET_TYPE,
160 'CIP', FCB.CIP_COST_ACCT,
161 FCB.ASSET_COST_ACCT),
162 decode(FAH.ASSET_TYPE,
163 'CIP', ' ',
164 FCB.DEPRN_RESERVE_ACCT),
165 fcb.category_id,
166 decode(TAH.ASSET_TYPE,
167 'CIP', TCB.CIP_COST_ACCT,
168 TCB.ASSET_COST_ACCT),
169 decode(TAH.ASSET_TYPE,
170 'CIP', ' ',
171 TCB.DEPRN_RESERVE_ACCT),
172 tcb.category_id,
173 ad.asset_number, ad.description, ad.tag_number, ad.serial_number,
174 ad.inventorial,
175 th.transaction_header_id;
176
177
178 begin
179 h_book := book;
180 h_request_id := request_id;
181
182 select fcr.last_update_login into h_login_id
183 from fnd_concurrent_requests fcr
184 where fcr.request_id = h_request_id;
185
186 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
187
188 select accounting_flex_structure, distribution_source_book
189 into h_acct_struct, h_dist_source_book
190 from fa_book_controls
191 where book_type_code = h_book;
192
193 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
194
195 select category_flex_structure
196 into h_cat_struct from fa_system_controls;
197
198 h_mesg_name := 'FA_RX_SEGNUMS';
199
200 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
201 BOOK => h_book,
202 BALANCING_SEGNUM => h_bal_seg,
203 ACCOUNT_SEGNUM => h_acct_seg,
204 CC_SEGNUM => h_cc_seg,
205 CALLING_FN => 'RECLASS');
206
207
208 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
209
210 open reclass_lines;
211 loop
212
213 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
214
215 fetch reclass_lines into
216 h_ccid,
217 h_period_name,
218 h_from_cost_acct,
219 h_from_reserve_acct,
220 h_from_category_id,
221 h_to_cost_acct,
222 h_to_reserve_acct,
223 h_to_category_id,
224 h_asset_number,
225 h_description,
226 h_tag_number,
227 h_serial_number, h_inventorial,
228 h_cost,
229 h_reserve,
230 h_thid;
231
232
233 if (reclass_lines%NOTFOUND) then exit; end if;
234
235 h_mesg_name := 'FA_RX_CONCAT_SEGS';
236 h_flex_error := 'GL#';
237 h_ccid_error := h_ccid;
238
239 fa_rx_shared_pkg.concat_acct (
240 struct_id => h_acct_struct,
241 ccid => h_ccid,
242 concat_string => h_concat_acct,
243 segarray => h_acct_segs);
244
245 h_flex_error := 'CAT#';
246 h_ccid_error := h_to_category_id;
247
248 fa_rx_shared_pkg.concat_category (
249 struct_id => h_cat_struct,
250 ccid => h_to_category_id,
251 concat_string => h_concat_tcat,
252 segarray => h_tcat_segs);
253
254 h_flex_error := 'CAT#';
255 h_ccid_error := h_from_category_id;
256
257 fa_rx_shared_pkg.concat_category (
258 struct_id => h_cat_struct,
259 ccid => h_from_category_id,
260 concat_string => h_concat_fcat,
261 segarray => h_fcat_segs);
262
263 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
264
265 insert into fa_reclass_rep_itf (
266 request_id, company, cost_center, expense_acct,
267 period_name, from_cost_acct, to_cost_acct, inventorial,
268 from_reserve_acct, to_reserve_acct, from_category,
269 to_category, asset_number, description, tag_number,
270 serial_number, cost, reserve, transaction_header_id,
271 created_by, creation_date, last_updated_by,
272 last_update_date, last_update_login) values (
273 request_id, h_acct_segs(h_bal_seg), h_acct_segs(h_cc_seg),
274 h_acct_segs(h_acct_seg),
275 h_period_name, h_from_cost_acct, h_to_cost_acct, h_inventorial,
276 h_from_reserve_acct, h_to_reserve_acct, h_concat_fcat,
277 h_concat_tcat, h_asset_number, h_description, h_tag_number,
278 h_serial_number, h_cost, h_reserve, h_thid,
279 user_id, sysdate, user_id, sysdate, h_login_id);
280
281 end loop;
282
283 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
284
285 close reclass_lines;
286
287 exception when others then
288 if SQLCODE <> 0 then
289 fa_Rx_conc_mesg_pkg.log(SQLERRM);
290 end if;
291 fnd_message.set_name('OFA',h_mesg_name);
292 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
293 fnd_message.set_token('TABLE','FA_RECLASS_REP_ITF',FALSE);
294 end if;
295 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
296 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
297 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
298 end if;
299
300 h_mesg_str := fnd_message.get;
301 fa_rx_conc_mesg_pkg.log(h_mesg_str);
302 retcode := 2;
303
304 end reclass;
305
306 END FARX_RC;