[Home] [Help]
PACKAGE BODY: APPS.FARX_CP
Source
1 PACKAGE BODY FARX_CP as
2 /* $Header: farxcpb.pls 120.5 2009/04/16 14:31:59 bridgway ship $ */
3
4 procedure cap (
5 book varchar2,
6 begin_period varchar2,
7 end_period varchar2,
8 request_id number default null,
9 user_id number default null,
10 retcode out nocopy number,
11 errbuf out nocopy varchar2) is
12
13 h_count number;
14 h_book varchar2(30);
15 h_precision number;
16 h_period1_pod date;
17 h_period2_pcd date;
18 h_life_year_month varchar2(10);
19 h_life_yr_mo_num number;
20 h_request_id number;
21 h_login_id number;
22
23 h_ccid number;
24 h_cip_cost_acct varchar2(25);
25 h_asset_cost_acct varchar2(25);
26 h_asset_number varchar2(15);
27 h_description varchar2(80);
28 h_tag_number varchar2(15);
29 h_serial_number varchar2(35);
30 h_inventorial varchar2(3);
31 h_dpis date;
32 h_method varchar2(15);
33 h_life_months number;
34 h_prod_capacity number;
35 h_adjusted_rate number;
36 h_cost number;
37
38 h_concat_acct varchar2(500);
39 h_acct_segs fa_rx_shared_pkg.Seg_Array;
40 h_acct_structure number;
41 h_bal_seg number;
42 h_cc_seg number;
43 h_acct_seg number;
44
45 h_mesg_name varchar2(50);
46 h_mesg_str varchar2(2000);
47 h_flex_error varchar2(5);
48 h_ccid_error number;
49
50 cursor cap_lines is
51 SELECT dhcc.code_combination_id,
52 FACB.CIP_COST_ACCT,
53 FACB.ASSET_COST_ACCT,
54 FADD.ASSET_NUMBER, FADD.DESCRIPTION,
55 fadd.tag_number, fadd.serial_number, fadd.inventorial,
56 FABKS.DATE_PLACED_IN_SERVICE,
57 FABKS.DEPRN_METHOD_CODE,
58 FABKS.LIFE_IN_MONTHS,
59 FABKS.PRODUCTION_CAPACITY,
60 FABKS.ADJUSTED_RATE,
61 ROUND(DECODE(FATRANS.TRANSACTION_TYPE_CODE,'CIP REVERSE',-1*(FABKS.COST),
62 FABKS.COST)*(SUM(FADH.UNITS_ASSIGNED)/FAHIST.UNITS),h_precision)
63 FROM GL_CODE_COMBINATIONS DHCC,
64 FA_DISTRIBUTION_HISTORY FADH,
65 FA_CATEGORY_BOOKS FACB,
66 FA_ASSET_HISTORY FAHIST,
67 FA_ADDITIONS FADD,
68 FA_BOOKS FABKS,
69 FA_TRANSACTION_HEADERS FATRANS
70 WHERE FATRANS.TRANSACTION_TYPE_CODE = 'ADDITION'
71 AND FATRANS.BOOK_TYPE_CODE = h_book
72 AND FATRANS.DATE_EFFECTIVE BETWEEN
73 h_period1_pod
74 AND nvl(h_period2_pcd, sysdate) -- fix for bug 2865813
75 AND EXISTS (SELECT NULL
76 FROM FA_ASSET_HISTORY AH
77 WHERE AH.ASSET_ID = FATRANS.ASSET_ID
78 AND AH.ASSET_TYPE = 'CIP')
79 AND FATRANS.ASSET_ID = FADH.ASSET_ID
80 AND FATRANS.BOOK_TYPE_CODE = FADH.BOOK_TYPE_CODE
81 AND FATRANS.DATE_EFFECTIVE >= FADH.DATE_EFFECTIVE
82 AND FATRANS.DATE_EFFECTIVE < NVL(FADH.DATE_INEFFECTIVE, SYSDATE)
83 AND FADH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
84 AND FATRANS.TRANSACTION_HEADER_ID = FABKS.TRANSACTION_HEADER_ID_IN
85 AND FABKS.ASSET_ID = FADD.ASSET_ID
86 AND FATRANS.TRANSACTION_HEADER_ID = FAHIST.TRANSACTION_HEADER_ID_IN
87 AND FATRANS.ASSET_ID = FAHIST.ASSET_ID
88 AND FAHIST.CATEGORY_ID = FACB.CATEGORY_ID
89 AND FACB.BOOK_TYPE_CODE = h_book
90 GROUP BY dhcc.code_combination_id,
91 FACB.CIP_COST_ACCT,
92 FACB.ASSET_COST_ACCT,
93 FADD.ASSET_NUMBER,
94 FADD.DESCRIPTION,
95 fadd.tag_number, fadd.serial_number, fadd.inventorial,
96 FABKS.DATE_PLACED_IN_SERVICE,
97 FABKS.DEPRN_METHOD_CODE,
98 FABKS.LIFE_IN_MONTHS,
99 FABKS.PRODUCTION_CAPACITY,
100 FABKS.ADJUSTED_RATE,
101 FATRANS.TRANSACTION_TYPE_CODE,
102 FABKS.COST,
103 FAHIST.UNITS,
104 FATRANS.ASSET_ID,
105 FABKS.DATE_EFFECTIVE;
106
107
108 begin
109
110 h_book := book;
111 h_request_id := request_id;
112
113 select fcr.last_update_login into h_login_id
114 from fnd_concurrent_requests fcr
115 where fcr.request_id = h_request_id;
116
117 h_mesg_name := 'FA_AMT_SEL_PERIODS';
118
119 select period_open_date
120 into h_period1_pod
121 from fa_deprn_periods
122 where book_type_code = h_book and period_name = begin_period;
123
124 select count(*) into h_count
125 from fa_deprn_periods where period_name = end_period
126 and book_type_code = h_book;
127
128 if (h_count > 0) then
129 select period_close_date
130 into h_period2_pcd
131 from fa_deprn_periods
132 where book_type_code = h_book and period_name = end_period;
133 else
134 h_period2_pcd := null;
135 end if;
136
137 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
138
139 select accounting_flex_structure
140 into h_acct_structure
141 from fa_book_controls
142 where book_type_code = h_book;
143
144 h_mesg_name := 'FA_DYN_CURRENCY';
145
146 select cur.precision into h_precision
147 from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
148 where bc.book_type_code = h_book
149 and sob.set_of_books_id = bc.set_of_books_id
150 and sob.currency_code = cur.currency_code;
151
152 h_mesg_name := 'FA_RX_SEGNUMS';
153
154 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
155 BOOK => h_book,
156 BALANCING_SEGNUM => h_bal_seg,
157 ACCOUNT_SEGNUM => h_acct_seg,
158 CC_SEGNUM => h_cc_seg,
159 CALLING_FN => 'CAP');
160
161 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
162
163 open cap_lines;
164 loop
165
166 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
167
168 fetch cap_lines into
169 h_ccid,
170 h_cip_cost_acct,
171 h_asset_cost_acct,
172 h_asset_number,
173 h_description,
174 h_tag_number,
175 h_serial_number,
176 h_inventorial,
177 h_dpis,
178 h_method,
179 h_life_months,
180 h_prod_capacity,
181 h_adjusted_rate,
182 h_cost;
183
184
185 if (cap_lines%NOTFOUND) then exit; end if;
186
187 h_mesg_name := 'FA_RX_CONCAT_SEGS';
188 h_flex_error := 'GL#';
189 h_ccid_error := h_ccid;
190
191 fa_rx_shared_pkg.concat_acct (
192 struct_id => h_acct_structure,
193 ccid => h_ccid,
194 concat_string => h_concat_acct,
195 segarray => h_acct_segs);
196
197 select decode(h_life_months, null, null,
198 to_char(floor(h_life_months/12)) || '.' ||
199 to_char(mod(h_life_months,12)))
200 into h_life_year_month
201 from dual;
202
203 h_life_yr_mo_num := fnd_number.canonical_to_number(h_life_year_month);
204
205 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
206
207 insert into fa_cap_rep_itf (
208 request_id, company, cost_center, expense_acct,
209 cip_cost_acct, asset_cost_acct, asset_number,
210 description, date_placed_in_service, method,
211 tag_number, serial_number, inventorial,
212 life_year_month, capacity, adjusted_rate, cost,
213 created_by, creation_date, last_updated_by,
214 last_update_date, last_update_login) values (
215 request_id, h_acct_segs(h_bal_seg), h_acct_segs(h_cc_seg),
216 h_acct_segs(h_acct_seg), h_cip_cost_acct,
217 h_asset_cost_acct, h_asset_number, h_description,
218 h_dpis, h_method, h_tag_number, h_serial_number, h_inventorial,
219 h_life_yr_mo_num, h_prod_capacity,
220 h_adjusted_rate, h_cost, user_id,
221 sysdate, user_id, sysdate, h_login_id);
222
223
224 end loop;
225
226 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
227
228 close cap_lines;
229
230 exception when others then
231 if SQLCODE <> 0 then
232 fa_Rx_conc_mesg_pkg.log(SQLERRM);
233 end if;
234 fnd_message.set_name('OFA',h_mesg_name);
235 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
236 fnd_message.set_token('TABLE','FA_CAP_REP_ITF',FALSE);
237 end if;
238 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
239 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
240 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
241 end if;
242
243 h_mesg_str := fnd_message.get;
244 fa_rx_conc_mesg_pkg.log(h_mesg_str);
245 retcode := 2;
246
247 end cap;
248
249 END FARX_CP;